cancel
Showing results for
Did you mean:
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:

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
6 REPLIES 6
Super User

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

Helper V

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.

Super User

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

Helper V

Hi @tamerj1 thanks for responding.

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

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper V

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

Announcements

#### 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.

#### 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!

#### 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