Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kumarm
Frequent Visitor

Grand total for Lastnonblank is wrong

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

kumarm_0-1598350411049.png

 

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.

 
 

 

 

 

1 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] )
    )

re.png

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.

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@kumarm , try a measure like

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

 

or move the calculation in this measure

 

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.

 

gtotal.JPG

 

 

 
 

 

 

 

 

 

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] )
    )

re.png

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.