DAX: Divide product by selected category

[TessSA]

Hello!

I have three product SKU categories (A, B, C) and would like to divide the total sales of each of categories A and B by one selected category (C). I have created the following measure in DAX:

% Sales on C =

var _category = SELECTEDVALUE('Won Quotes w  Line Items from 2023 - PBI'[SKU Category])

var _value = SELECTEDVALUE('Won Quotes w  Line Items from 2023 - PBI'[Quote Line Item: Total Price (converted).amount])

var _total = [Total C Sales]

var _result = DIVIDE(_value,_total,"")

return _result

It is calculating the sales of A, B and C categories divided by C. Is there a way to exclude C category from being calculated in the result? It's always going to show 100%... 

See attached screenshot:

[TessSA]

Thank you very much, that worked like a charm!

[rohit1991 · Accepted solution]

Here a few steps in oder to solve your problem. hope this helps:

1. Current Measure Logic:

   • The current measure is dividing the sales of each category (A, B, and C) by the sales of category C.
   • As you mentioned, category C always shows 100%, which is expected because it's dividing the sales of category C by itself.

2. Excluding Category C:

   • The issue arises because the calculation doesn’t differentiate category C from the other categories.
   • You need to add a filter condition that excludes category C when calculating the percentage for categories A and B.

3. Solution Approach:

   • Use a Filter Condition: Modify the measure to apply a filter that only calculates the division for categories A and B, but not for C.
   • Conditional Calculation: You can include a condition to check if the selected category is not "C" and proceed with the calculation only for A and B.

4. Potential Solutions:

   • IF Statement: Use an IF statement to check if the category is C, and if so, return a blank or another result instead of performing the division.
   • FILTER Function: Apply the FILTER function to exclude the C category in your calculations.

5. Outcome:

   • This should result in a correct percentage calculation for categories A and B relative to category C, while category C does not perform a calculation of 100%.

In short, modify the measure logic to exclude category C during the calculation, ensuring it does not impact the result.

% Sales on C =
var _category = SELECTEDVALUE('Won Quotes w Line Items from 2023 - PBI'[SKU Category])
var _value = SELECTEDVALUE('Won Quotes w Line Items from 2023 - PBI'[Quote Line Item: Total Price (converted).amount])
var _total = [Total C Sales]

-- Check if the selected category is not "C" and perform the division only for A and B
var _result = IF(_category <> "C", DIVIDE(_value, _total, ""), BLANK())

return _result