cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sujit_Thakur
Solution Sage
Solution Sage

Matrix total

Dear PBI Family ,
PLease can anyone help me with solving this issue 
Row total of my matrix is showing wrong value 
As shown below both rows are measure and both rows have same value but still different total ?
Row Totak issue.PNG
Please help

@nickyvv  


regards ,
Thakur Sujit

1 ACCEPTED SOLUTION

@amitchandak  and @v-chuncz-msft  and @AntrikshSharma 


i am super happy i got the problem , it was just the filter context , i tried to understand and then yes i did it!!!!
i just made one more DAX in which i told power bi to see if my model column has only one value then ok give me Expected dist measure but if more than one model is avialable in filter context then sum row wise the expected dist measure .

New DAX = IF(HASONEVALUE(Query1[Model]),[Exepected_dist],Sumx(values(Query[model]),[Expected_dist]))
I hope this helps to someone like me who would have spend almost 2 days stucked in one thing 


Regards ,

Thakur Sujit

View solution in original post

7 REPLIES 7
Sujit_Thakur
Solution Sage
Solution Sage

Dear @amitchandak sir ,

Please help , I can't find another way to solve this

Regards

Thakur Sujit

Sujit_Thakur
Solution Sage
Solution Sage

Dear @amitchandak sir   , 
let me please elaborate on this doubt ,
following is my example data table 

 

Datemodel MIN_ODOMAX_ODO
8/8/2020A200.29250.39
8/9/2020A260269
8/10/2020A00
8/11/2020A282291
8/8/2020B500550
8/9/2020B570589
8/10/2020B590600
8/11/2020B601610
8/8/2020C100110
8/9/2020C111119
8/10/2020C130156
8/11/2020C156160

 

 

Now in this table there is two measure .
1)Dist_rec = SUMX(Query1,'Query1'[MAX_ODO]) - SUMX(Query1,'Query1'[MIN_ODO]) 

2) Expected_Dist = Var MINK = MINX(FILTER(Query1,'Query1'[MIN_ODO] <> 0),'Query1'[MIN_ODO])
                               var MAXK = MAXX(Query1,Query1[MAX_ODO])
                               var result = MAXK - MINK
                                return result



Now when I use this two measure in matrix , first measure works good but second shows wrong row totals .
 @amitchandak  sir please help me with this and yes , I also have a Date slicer and Model drop down list , I hope the solution which you will suggest will also work when i apply drop down model filter or when i chang date slicer

Dear @v-chuncz-msft , 

I saw some time ago you gave solution to such problem , I would be greatly thank ful to you if you can help here too

 

Please help, got stucked from 2 days

Regards 

Thakur Sujit

@amitchandak  and @v-chuncz-msft  and @AntrikshSharma 


i am super happy i got the problem , it was just the filter context , i tried to understand and then yes i did it!!!!
i just made one more DAX in which i told power bi to see if my model column has only one value then ok give me Expected dist measure but if more than one model is avialable in filter context then sum row wise the expected dist measure .

New DAX = IF(HASONEVALUE(Query1[Model]),[Exepected_dist],Sumx(values(Query[model]),[Expected_dist]))
I hope this helps to someone like me who would have spend almost 2 days stucked in one thing 


Regards ,

Thakur Sujit

View solution in original post

The version of your code with SUMX works becuase at the grand total you are summing both the columns and then subtracting, but in the second one at the grand total you are just subtrating the Min of MINODO from Max of MAXODO with give you a large number and that is correct becuse at the grand total the MIN would be for the first date and the MAX will be for the last date..

amitchandak
Super User
Super User

@Sujit_Thakur , can you share formula, it may be because of row context. You might have to use values or summarize

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Dear @amitchandak ,
following is DAX

 
 
Dist =var MINK =MINX(FILTER(Query1,Query1[trip_min_odo]<>0),Query1[trip_min_odo])
         var MAXK =MAXX(Query1,Query1[trip_max_odo])
          var result = MAXK - MINK
return result

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors