cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
julesdude
Helper V
Helper V

Getting Correct Grand Totals Following Measure Column

Hi there,

I've had some valuable help here building the following DAX measure that will provide me with totals based on conditions to achieve this:

julesdude_0-1659439081011.png

 

DAX measure is:

Measure = 

VAR _0 = LASTDATE ( 'Table'[Date] )
VAR _1 = CALCULATE ( LASTDATE( 'Table'[Date] ) , ALLEXCEPT ( 'Table' ,'Table'[Country] ,'Table'[Asset Reference] ) )
VAR _2 = IF ( _1 = _0 , SUM ('Table'[Amount] ) , BLANK() )

RETURN

_2

 

However, as you can see in the total in the table above, it's not a reflection of what is in the column.

 

How can I achieve this?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@julesdude 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...

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @julesdude 

where exactly is the problem. I failed to spot tge error!

Hi @tamerj1 
In the top table of the screenshot I pasted, far right column 'Measure New', the values the measure is displaying aren't adding up to the Total displayed. 
That means it's a problem for the pie chart I'm compiling underneath as the figures in that pie chart aren't actually representative of the table above it. The table beneath the pie chart is basically the same data but in a table, to illustrate it another way.

So, I'm hoping for a solution around this common problem.

@julesdude 

Sorry I was so busy yesterday I diyhave the chance to look into this. Can you please describe in works what this measure is intended to calculate? Thank you

Hi @tamerj1 thanks for responding.

I managed to get the example in post Greg posted to work for me.

Greg_Deckler
Super User
Super User

@julesdude 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...

Hi @Greg_Deckler 

Many thanks for the valuable link and your post. The Final Word worked brilliantly and I shall refer to this in future whenever i hit this issue. 

I added to the above code creating a new measure which references my original measure, and it is this new measure that the visualisation now uses directly,

Thanks again

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors