|| Dynamic Segmentation : The formula ||

[somnath6309]

Hi, Pls vide the folloiwng pic :

Here, we use SUMX to get the right total as shown in the Matrix. However, if we omit the SUMX, the formula still works but we get the wrong summation at the Grand Total.
Now, in line no. 05 of the code, FILTER is an iterator which opens the row context on the 'Customer' table ( row 06 of the code). Within the iteration, the measure [Sales Amount] invokes context transition and we get sales amount per customer.
However, in line no. 8 of the code, we write : [Sales Amount] > Segments[MinSale].
Here, we have a tendency to assume that we are referring the Minimum value of the whole column "MinSale" in the Segments table. However, the code in line no. 08 is evaluating the Minimum Sale row by row of the Segments table. Pls see the below pic for reference:

Now, the question is how the iteration is happening in the "Segments" table ? Here, pls assume that we are not using SUMX, the code starts from CountRows.

I was told that If I omit the SUMX(Segments, ) from the measure and directly start from COUNTROWS, then the Segments[MinSale] and Segment[MaxSale] wil raise an error because there is no row context on the Segment table. I have used the following code in the matrix and the same is working as shown below in the gif image :

CustSegV2 =

COUNTROWS (
FILTER (
Customer,
[Sales Amount] > MIN ( Segments[MinSale] ) &&
[Sales Amount] <= MAX ( Segments[MaxSale] )
) )

Now, the question still exists:

Q-1:

In line no. 06 of the DAX code, how the formula MIN is accessing the row context of the "Segments" table so that we are getting the minimum value row by row of the "MinSale" Column ?
For your reference. I have attached the PBIX file below.

In that file there is another version of the code using CALCULATE which is as follows;

However, the code returns some result, however the total is not correct:

Q-2

Here, I have the same question, how the formula MIN is accessing the row context of the "Segments" table so that we are getting the minimum value row by row of the "MinSale" Column ?

Link to Pbix file is attached below.

Regards,

Somnath

https://1drv.ms/u/c/bcaa03301de859ce/IQCa_E-lChlHTYiC9jckNvDiAUGv8brLzuQ0SqK0U6rKmQ4?e=PlLegH 

[MFelix]

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/

[somnath6309]

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

[MFelix · Accepted solution]

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.