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
analyst312
Frequent Visitor

Matrix not totaling correctly on the column total

I have a matrix and the measure that i have in place is not totaling correctly on the grand total. I have seen a few other questions similar to this but i dont fully understand how to fix this. 

 

For the highlighted totals, i would expect them to be 8 and 4, not 4 and 5.

 

Any ideas?

 

The measure is:

 

Events = IF(MONTH(MAX(FTE_Table[Posted Dt.]))=12,[Ranking],0)
 

 

image.png

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @analyst312 ,

 

Try this:

1. Create a new column.

Month = MONTH(FTE_Table[Posted Dt.])

2. Create a measure.

Events =
IF (
    HASONEFILTER ( FTE_Table[Posted Dt.].[Month] ),
    IF ( MAX ( FTE_Table[Month] ) = 12, [Ranking], 0 ),
    CALCULATE ( [Ranking], FTE_Table[Month] = 12 )
)

event.PNG

 

 

Best Regards,
Icey

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

3 REPLIES 3
Icey
Community Support
Community Support

Hi @analyst312 ,

 

Try this:

1. Create a new column.

Month = MONTH(FTE_Table[Posted Dt.])

2. Create a measure.

Events =
IF (
    HASONEFILTER ( FTE_Table[Posted Dt.].[Month] ),
    IF ( MAX ( FTE_Table[Month] ) = 12, [Ranking], 0 ),
    CALCULATE ( [Ranking], FTE_Table[Month] = 12 )
)

event.PNG

 

 

Best Regards,
Icey

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

amitchandak
Super User
Super User

calculate(IF(MONTH(MAX(FTE_Table[Posted Dt.]))=12,[Ranking],0),values(FTE_Table[Posted Month])).

 

You need to have context. Please refer to my blog on date diff, how to context in measure calculation:https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

Greg_Deckler
Super User
Super User

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

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.