Forum Discussion
Matrix total
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
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
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..