Grand total for Lastnonblank is wrong

[kumarm]

I am trying to get lastnonblank value in a measure .Its returning the correct value at row level.But at Grand total level its returning wrong value.It seems that it is taking July grand total insted of adding for each operation.

Total Income Forecast DP YE - Report test =

VAR LASTNONBLANK_1 =

CALCULATE (

[Total Income Forecast DP - Report],

LASTNONBLANK (

'Dim Period'[PeriodDate],

CALCULATE ([Total Income Forecast DP - Report] )

)

)

return

LASTNONBLANK_1

When I force to calculate grand total using sumx.It also return me wrong value.

Total Income Forecast DP YE - Report test =

VAR LASTNONBLANK_1 =

CALCULATE (

[Total Income Forecast DP - Report],

LASTNONBLANK (

'Dim Period'[PeriodDate],

CALCULATE ([Total Income Forecast DP - Report] )

)

)

return

If(HASONEVALUE('Dim Implementer'[Operation]),LASTNONBLANK_1,sumx(VALUES('Dim Implementer'[Operation]),LASTNONBLANK_1))

 

After using above formula the grand total becomes 45,668,410

The correct Grand total value should be :-13,784,559

Thank you in advance.

[Greg_Deckler]

@kumarm This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[amitchandak]

@kumarm , try a measure like

sumx( values(Table[operations]),[Total Income Forecast DP YE - Report test ])

or move the calculation in this measure

[kumarm]

Thanks amitchandak  for your Quick reply.

The solution that you suggested it works only when I create a separate measure using the existing one.

But If I want to see data based on another dimesion the value seems to be wrong.

I want to see the grand total correct when I select other dimension as well.

Can you please suggest how to handle this.

[v-yingjl · Accepted solution]

Hi @kumarm ,

You can create this measure:

Result = 
VAR lastnoblank =
    CALCULATE (
        [Total Income Forecast DP - Report],
        LASTNONBLANK ( 'Dim Period'[PeriodDate], [Total Income Forecast DP - Report] )
    )
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[Operation],
        "lastnoblank",
            CALCULATE (
                [Total Income Forecast DP - Report],
                LASTNONBLANK (
                    'Dim Period'[PeriodDate],
                    CALCULATE ( [Total Income Forecast DP - Report] )
                )
            )
    )
RETURN
    IF (
        HASONEVALUE ( 'Dim Period'[PeriodDate].[Month] ),
        lastnoblank,
        SUMX ( tab, [lastnoblank] )
    )

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.