Total on the table is not correct

[vjnvinod]

Dax issue

below is my dax

Cumulative Actual + Forecast =

IF(

    CALCULATE(

        SUM('GroupOPEXCAPEX'[Value]),

        'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"

    ) > 0,

    -- Use Cumulative Actuals for months with Actuals

    CALCULATE(

        SUM('GroupOPEXCAPEX'[Value]),

        FILTER(

            ALLSELECTED('GroupOPEXCAPEX'),

            'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) &&

            'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"

        )

    ),

    -- Use Forecast for months without Actuals

    CALCULATE(

        SUM('GroupOPEXCAPEX'[Value]),

        'GroupOPEXCAPEX'[Actual/Budget] = "Forecast"

    )

), as you can see everything is fine, but the total is not adding the forecast and acutals

should be ideally 291+30

is there way to resolve this?

[Suresh604]

Hi @vjnvinod ,
You can follow the below steps:

1. • First, create individual measures for Actuals and Forecasts. This will help us clearly separate the calculations.

     Create Separate Measures for Actuals and Forecasts:

   Total Actuals = CALCULATE( SUM('GroupOPEXCAPEX'[Value]), 'GroupOPEXCAPEX'[Actual/Budget] = "Actuals" ) Total Forecasts = CALCULATE( SUM('GroupOPEXCAPEX'[Value]), 'GroupOPEXCAPEX'[Actual/Budget] = "Forecast" )
   • Now, create a measure to calculate the cumulative value for Actuals.

     Create the Cumulative Measure:

2. Cumulative Actuals = CALCULATE( SUM('GroupOPEXCAPEX'[Value]), FILTER( ALLSELECTED('GroupOPEXCAPEX'), 'GroupOPEXCAPEX'[DateColumn] <= MAX('GroupOPEXCAPEX'[DateColumn]) && 'GroupOPEXCAPEX'[Actual/Budget] = "Actuals" ) )
   • Next, combine the cumulative actuals and forecast values into a single measure.

     Combine the Cumulative Actuals and Forecasts:

3. Cumulative Actual + Forecast = IF( [Total Actuals] > 0, -- Use Cumulative Actuals for months with Actuals [Cumulative Actuals], -- Use Forecast for months without Actuals [Total Forecasts] )
4. • To ensure the total sums up correctly, you might need to adjust the measure to handle totals explicitly.

     Adjust the Total Calculation:

   Cumulative Actual + Forecast Total = IF( HASONEVALUE('GroupOPEXCAPEX'[DateColumn]), [Cumulative Actual + Forecast], -- Calculate total by summing both actuals and forecasts [Total Actuals] + [Total Forecasts] )

Explanation:

• Total Actuals and Total Forecasts: These measures calculate the sum of actual and forecast values separately.
• Cumulative Actuals: This measure calculates the cumulative sum of actual values up to the current date.
• Cumulative Actual + Forecast: This measure uses the cumulative actuals if there are actual values; otherwise, it uses forecast values.
• Cumulative Actual + Forecast Total: This measure ensures that the total row sums up both actuals and forecasts correctly.

Final Steps:

• Replace your existing measure with the "Cumulative Actual + Forecast Total" measure in your visual.

This approach should ensure that your totals add up correctly, reflecting both the cumulative actuals and forecast values as expected.

If this solutions worked, please accept the solution so others can get it as well.  

Regards,

Suresh Patra
https://www.linkedin.com/in/sureshpatra

[danextian]

Hi @vjnvinod 

Your calculation needs to be evaluated for each month row and then summed up.

Cumulative Actual + Forecast =
SUMX (
    VALUES ( GroupOPEXCAPEX[month] ),
    IF (
        CALCULATE (
            SUM ( 'GroupOPEXCAPEX'[Value] ),
            'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
        ) > 0,
        -- Use Cumulative Actuals for months with Actuals
        CALCULATE (
            SUM ( 'GroupOPEXCAPEX'[Value] ),
            FILTER (
                ALLSELECTED ( 'GroupOPEXCAPEX' ),
                'GroupOPEXCAPEX'[DateColumn] <= MAX ( 'GroupOPEXCAPEX'[DateColumn] )
                    && 'GroupOPEXCAPEX'[Actual/Budget] = "Actuals"
            )
        ),
        -- Use Forecast for months without Actuals
        CALCULATE (
            SUM ( 'GroupOPEXCAPEX'[Value] ),
            'GroupOPEXCAPEX'[Actual/Budget] = "Forecast"
        )
    )
)

[vjnvinod]

@danextian  @Ashish_Mathur 

Provided code doesn't work either, see below, instead of cumulative actuals it shows actuals now

[Ashish_Mathur]

As i mentioend in my previous message, since you do not have a Calendar table, you are facing problems.

[vjnvinod]

@Ashish_Mathur 

thanks Ashish, is there a workaround without Calendar table, or is it mandatory to create a calendar table, as the dataset is very small.

[Ashish_Mathur]

Always preferred.

[danextian]

There's a reason why using a separate dates/calendar table is a  best practice. It simplifies time intelligence calculations. While time intelligence can be done sans this table, you will start seeing unexpected result once you add columns not included in your filter context argument. You can't just use ALL(facttable) as this will return the same value regardless of any filter coming  from that table.  Anyway, try this:

Cumulative Actual + Forecast2 =
IF (
    NOT ( HASONEVALUE ( GroupOPEXCAPEX[month] ) ),
    SUMX (
        VALUES ( GroupOPEXCAPEX[month] ),
        IF (
            [Actuals] > 0,
            [Actuals],
            -- Use Forecast for months without Actuals
            [Forecast]
        )
    ),
    [Cumulative Actual + Forecast]
)

[vjnvinod]

@danextian  sorry it doesn't work 

[danextian]

Use the original formula that you used for Cumulative Actual + Forecast. The logic is , at the total level,  to add the total actuals + forecast (if actuals is 0, 291M + 31M ) but at the month level to return Cumulative Actual + Forecast measure

[vjnvinod]

@danextian  didn't follow, can you please help with the complete dax code?

[danextian · Accepted solution]

Please refer to the screenshot

[vjnvinod]

@danextian  any way to show the cumulative of Forecast as well? also i am trying to plot this in line graph and not able to find a way to distinguish the Forecast and actuals

any suggestions?

[danextian]

You will need to write a separate measure for that which is very similar to below

CALCULATE (
    SUM ( 'GroupOPEXCAPEX'[Value] ),
    FILTER (
        ALLSELECTED ( 'GroupOPEXCAPEX' ),
        'GroupOPEXCAPEX'[DateColumn] <= MAX ( 'GroupOPEXCAPEX'[DateColumn] )
            && 'GroupOPEXCAPEX'[Actual/Budget] = "Forecast"
    )
)

The forecast line will continue until December. If  you want it to be until when there is a value for actuals, you can include a condition:

IF (
    [Actuals] <> 0,
    CALCULATE (
        SUM ( 'GroupOPEXCAPEX'[Value] ),
        FILTER (
            ALLSELECTED ( 'GroupOPEXCAPEX' ),
            'GroupOPEXCAPEX'[DateColumn] <= MAX ( 'GroupOPEXCAPEX'[DateColumn] )
                && 'GroupOPEXCAPEX'[Actual/Budget] = "Forecast"
        )
    )
)

a 

[vjnvinod]

@danextian 

i want to plot acutal and forecast in one line, actuals should be plane and forecast like a dotted line

problem i am facing is the last dax you shared is not cumulating the remaining months forecast with the cumulated actuls, so it has to be 295+15 and so on so forth til december

some tweaking needs in that measure, so to be able to cumulate the forecast number as well with actuals

[danextian]

You will still need two separate measures: Cumulative Actual and Forecast (not cumulative), or you won't be able to distinguish them. That aside, there will be a gap between Oct and Nov in your line chart as the two measures will not be connected. The workaround is to include either the forecast for Nov in the Cumulative Actual measure or include Oct in the Remaining Forecast measure.

Forecast with prev month's cumulative value =
IF (
    SELECTEDVALUE ( GroupOPEXCAPEX[Month] ) --last month with actuals
        = CALCULATATE (
            LASTNONBLANK ( GroupOPEXCAPEX[Month], [Actuals] ),
            ALL ( GroupOPEXCAPEX[Month] )
        ),
    [Cumulative],
    [Remaining Forecast]
)

,

[vjnvinod]

@danextian  som kind of tupple error

[danextian]

There is an extra opening parenthesis in your measure:

[Ashish_Mathur]

Hi,

The very first blunder is that you do not have a Calendar Table.  Anyways, this measure pattern should work

Measure = SUMX(Calendar[Month name],[Cumulative Actual + Forecast])

In the visual, the Year and Month name fields should be dragged from the Calendar Table.  In your existing meaure as well, changes will have to be made but with the information which you have shared, this is the max i can help you.