Grand Total sum error of multiple calculation in masure

[or_barak]

Hello,

I've got a matrix with 3 rows of hierarchy (Company, Channel Type, Channel Name).

One of the values in the matrix is a calculated measure of end of month sales prediction.

Each Channel is part of group (there are 3 groups) that calculate the sales prediction differently.

Spoiler

Total_Prediction =

var a = [Prediction]+(([Total_Orders_B2B]-[Daily_Sales])/[Total_Working_Days])*[Supply_Days_ Passed]

var b = [Total_Orders_B2B]*([Supply_Days_Passed]-[Total_Working_Days])+[Total_Sales]

var c = if([Total_Orders_B2B]>[Daily_Sales],a,b)

var e = if([Total_Orders_B2B]>0,c,SWITCH(SELECTEDVALUE('Dim_Channels'[Group]), "Sales_Representative",[Total_Sales], "General",[ Prediction])+[Total_Orders_B2C])

var d = if(SELECTEDVALUE('Dim_Channels'[Group])= "Corporate_Teams", [Total_Sales],e)

return d

The problem is that the sum at the grand total filed is not equal to the sum of the measure in the lines above it. Does anyone one knows what is the problem ?

[or_barak]

It seems as the "if statements" in the measure runs over each parent or child hierarchy so the calculation are false. is there a way to make the parents level in the hierarchy just sum the child calculations ?

[jennratten]

What it is actually doing is detecting which level of the hierarchy is in the current row and then returning the appropriate calculation for that row.  Check out the second link I sent - that will probably be more insightful.
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/ 

[jennratten]

This is a good article that explains how to create your measure so that the totals sum correctly with the hierarchy.

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/ 

[or_barak]

Thanks ! can you also tell me if you have any reference to this kind of solutions with a bit more complex calculation like I did ?

[jennratten · Accepted solution]

The linked webpage should provide you with the needed solution for displaying correct totals for each level of the hierarchy along with the correct grand total.  For the different calculations based on the group, you can create separate measures for each version of the calculation and then include them in the measure that goes on in the visual for which the script includes SWITCH and ISINSCOPE to identify the context and display the appropriate calculation.

Here is another page that explains that.

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/ 

[or_barak]

Thank you ! it worked !!!

[jennratten]

Great news!!