Forum Discussion
|| Dynamic Segmentation : The formula ||
Hi somnath6309 ,
That is refering to the the fact that you are using the values from the segment table within each row meaning that for each line you only have a single line if you took for example the Very Low you have min 0 maximum 75 this is what is going to be applied to the specific calculation and the result gets the exact result.
So the row context is coming directly from your segment table since you used it in your lines in the matrix visual and then applying that to the calculation.
Hi somnath6309 ,
This is a question about the row context when you are at the level of the Segment you are only getting one row of your segments table and a single value for each of the rows the row context is coming from the Segment column in that you are using in the table:
Very Low: Min 0 Max 75
Very High Min 1000 Max 99999999
However when you get to the grand total you are getting a table with two rows and when you do the min and the MAX you are getting
MIN (0, 1000) = 0
MAX( 75, 99999999) = 99999999
That is why your total is not matching the rows you have selected because the row context is not the same you go from one row table to two rows table.
When you use the SUMX you are changing the way the calculation is done so instead of looking at the values in an aggregated way what you are doing is first to get the segments table and for that segment count all rows that match that specific line in the end you do the sum.
Basically you have the table
And on top of this table you then do the sum of the values, if you look at the DAX Query you can see that the calculation for each row will be a SUM of the previous table with the filter of the segment and not the countrows, so even for the grand total you will get a sum of the previous values and not the values between very low and very high.
When you remove the SUMX what happens is that the row context of the segment is removed and then you get again the calculation based on MIN and MAX of all the values of the segment selected.
Heres is a complete blog and video about that
https://www.sqlbi.com/articles/why-power-bi-totals-might-seem-inaccurate/
https://www.sqlbi.com/tv/why-power-bi-totals-might-seem-wrong/
Hi,
My question was not regarding the wrong grand total. We have to use SUMX if want the correct grand total. However the following version of the code works for every cell of the matrix other than the Grand Totals ( we can make it OFF from Visual options) :
Here, if we view the cells highlighted in RED in the below matrix, the figures 351, 302 are correct. ( see the below pic )
Now, the question is : when the code is executed without SUMX, how the row context is opened in the "Segments" table so that the MIN formula can access the Minimum Sale row by row ?
Regards,
Somnath
Hi somnath6309 ,
That is refering to the the fact that you are using the values from the segment table within each row meaning that for each line you only have a single line if you took for example the Very Low you have min 0 maximum 75 this is what is going to be applied to the specific calculation and the result gets the exact result.
So the row context is coming directly from your segment table since you used it in your lines in the matrix visual and then applying that to the calculation.
