topic Re: DAX: Divide product by selected category in DAX Commands and Tips

DAX: Divide product by selected category

TessSA — Fri, 15 Nov 2024 10:52:56 GMT

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:

Re: DAX: Divide product by selected category

rohit1991 — Wed, 30 Jul 2025 12:55:55 GMT

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

Re: DAX: Divide product by selected category

TessSA — Fri, 15 Nov 2024 12:59:51 GMT

Thank you very much, that worked like a charm!