cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wpf_
Post Partisan
Post Partisan

Why aren't my Matrix totals not correct?

I have a matrix that have have dates in rows, locations in columns, and current months sum for each location, previous years of the exact month sum, and percentage change between the two as the values.  However the total of each row is not correct.  In the image below, i omitted the different column locations.  The values of each column are accurate as i crossed referenced with the real data, but the total for current, prev (in red) are not when I add them up manually.  Is there any setting that is causing this? Thanks.

 

wpf__0-1593730847317.png

 

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@wpf_ , if you formula/measure use row context to get the calculation then Grand total also need that,try like this

sumx(Summarize(Table, Table[Year], Table[Month], Table[?], "_1",[Measure]),[_1])

 

Where Table[?] is column group by

and Measure is your current measure



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi @amitchandak ,

 

Thanks for your reply.  I kind of understand why my totals aren't correct.  Can you please see my reply above.  This is a head scratcher.  Kind of like the chicken or the egg scenario. Thanks. 

AllisonKennedy
Super User III
Super User III

What is the formula you're using for the three measures? See if this post helps:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Hi @AllisonKennedy ,

 

Thanks for your reply.  I tried the suggestion that you refered me to but it didnt work.  But I kind of know why it is doing it now.  To better explain it here is my table again with the locations 21 - 30 (each representing a location).  

 

wpf__0-1593793939693.png

Just focusing on the 'current' measure.  Location 30 needs to have its value multipled by 2.1, and all other locations are just sumed up regularly. The values are taken by two methods: method1 and method2. 

 

Current = IF(SELECTEDVALUE('Table'[Location])=30,
SUMX('Table', ('Table'[Method1]+'Table'[Method2])*2.1),
SUMX('Table', 'Table'[Method1] + 'Table'[Method2])
)
 
So the problem is when the Total Current is summed, it is getting the current from all locations and that includes location 30.  And it does not use the calculated Current of Location 30, becaue of the nature of the condition.  
 
I am trying to find logic to take in the Current of 30, but if I do that, the Current of the other locations would use that as well in their values.  Is there any way to reference the Total column so I can include that in the IF statement.  Or do you have a better alternative from what I am doing?  Thanks. 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors