cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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 ?

@nickyvv

regards ,
Thakur Sujit

1 ACCEPTED SOLUTION
Solution Sage

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

7 REPLIES 7
Solution Sage

Dear @amitchandak sir ,

Regards

Thakur Sujit

Solution Sage

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

 Date model MIN_ODO MAX_ODO 8/8/2020 A 200.29 250.39 8/9/2020 A 260 269 8/10/2020 A 0 0 8/11/2020 A 282 291 8/8/2020 B 500 550 8/9/2020 B 570 589 8/10/2020 B 590 600 8/11/2020 B 601 610 8/8/2020 C 100 110 8/9/2020 C 111 119 8/10/2020 C 130 156 8/11/2020 C 156 160

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

Solution Sage

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

Regards

Thakur Sujit

Solution Sage

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

Community Champion

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

Super User

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

Solution Sage

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors