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]

Hi @TessSA 

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

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.

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.

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.

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.

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