Decomposition Tree - Categories not following

[PaulBethancourt]

I have a Decomposition Tree visual that is based on the total wholesale price.

I split it out by Priority. (>= $20,000 is priority one, >= $10,000 is priority two, all else is priority three)

The next level is by Customer.  I  would expect it to show me Priority One customers with wholesale price >= $20,000 but I get customers as low as $1,000.  This is my calculated column for the priority:

Priority Cust Total = if(Calculate(sum(FACT_OPEN_RMA_DTL2_V[Wholesale Price]),

allexcept(FACT_OPEN_RMA_DTL2_V,FACT_OPEN_RMA_DTL2_V[Customer Name]))>=20000,"One",

if(Calculate(sum(FACT_OPEN_RMA_DTL2_V[Wholesale Price]),

allexcept(FACT_OPEN_RMA_DTL2_V,FACT_OPEN_RMA_DTL2_V[Customer Name]))>=10000,"Two","Three"))

 Top Results:

Lower Results:

Please help if you can.

[vaibhavkale570]

hello,

The issue you are encountering is likely due to the way you are using the ALLEXCEPT function within the CALCULATE function.

The problem is that the ALLEXCEPT function is removing all filters from the FACT_OPEN_RMA_DTL2_V table except for the ones specified in the arguments, which are the customer names in this case. This means that the SUM function inside the CALCULATE is aggregating the wholesale prices across all customers, not just the ones for the specific priority being evaluated.

To fix this issue, you need to modify the CALCULATE function to only consider the wholesale prices for the specific priority's customers. One way to achieve this is by using the FILTER function along with the ALLEXCEPT function. Here's the updated calculated column:
Priority Cust Total =
IF(
CALCULATE(
SUM(FACT_OPEN_RMA_DTL2_V[Wholesale Price]),
FILTER(
ALLEXCEPT(FACT_OPEN_RMA_DTL2_V, FACT_OPEN_RMA_DTL2_V[Customer Name]),
[Your Priority Column] = "One"
)
) >= 20000,
"One",
IF(
CALCULATE(
SUM(FACT_OPEN_RMA_DTL2_V[Wholesale Price]),
FILTER(
ALLEXCEPT(FACT_OPEN_RMA_DTL2_V, FACT_OPEN_RMA_DTL2_V[Customer Name]),
[Your Priority Column] = "Two"
)
) >= 10000,
"Two",
"Three"
)
)







In the above code, replace [Your Priority Column] with the appropriate column name that contains the priority levels for each customer. The FILTER function will restrict the wholesale price calculations to the specific priority level's customers, and then the SUM function inside the CALCULATE will give you the correct total wholesale price for each customer based on their priority level.

[PaulBethancourt]

Hi vaibhavkale570,
Thanks for the assisstance.  I was with you up until the line:
[Your Priority Column] = "One".
I'm not sure what that refers to.  I tried several iterations without any luck.