Forum Discussion
Measure DAX division not working as expected - skipping rows
Hello, I have a table like below (see attached sceenshot):
| Opportunity Name | SKU Category | Total Price | % Sales on X |
| A | COM | 2.823,93 € | 2,09% |
| A | CONFIG | - € | 0,00% |
| A | X | 134.919,31 € | |
| A | SUP | 7.840,00 € | 5,81% |
| B | ACC | 4.125,45 € | |
| B | COM | 2.397,67 € | 0,50% |
| B | CONFIG | - € | 0,00% |
| B | X | 482.572,80 € | |
| B | SUP | - € | 0,00% |
| C | ACC | 107.999,35 € | |
| C | COM | 9.294,00 € | |
| C | FL | 3.570,00 € | 0,94% |
| C | X | 380.010,33 € | |
| C | SUP | - € | 0,00% |
| D | COM | 17.200,00 € | 2,07% |
| D | CONFIG | - € | 0,00% |
| D | X | 831.628,76 € | |
| D | SP | 44.772,75 € | 5,38% |
Where "% Sales on X" is a measure calculated as follow:
Thank you, I had to adapt the DAX a bit but I was able to fix it by using SUMX for my value as follows:
% Sales on X =var _category = SELECTEDVALUE('Table'[SKU Category])var _value = SUMX('Table', 'Table'[Total Price])var _total = [Total X Sales]var _result = IF(_category <> "X", DIVIDE(_value, _total, ""), BLANK())return _resultMy "Total Price" column in my data set is alreayd the Qty * Unit Price.My total variable, [Total X Sales] was already being calculated correctly.
5 Replies
Hello TessSA ,
You can try to use a SUMX nested in a CALCULATE in a VAR for your TOTALX measure, ex:
VAR _category = SELECTEDVALUE('Table'[SKU Category])
VAR _Total Sales = SUMX('Table',
[Price] * [Quantity])
VAR _Total X = CALCULATE(
SUMX('Table',
[Price] * [Quantity]),
FILTER('Table',
[SKU Category] = "X"))
RETURN
IF(
CALCULATE(
MAX('Table'[SKU Category]) 'Table'[SKU Category] = _category) <> "X",
_Total Sales/_Total X, BLANK())
)Thank you, I had to adapt the DAX a bit but I was able to fix it by using SUMX for my value as follows:
% Sales on X =var _category = SELECTEDVALUE('Table'[SKU Category])var _value = SUMX('Table', 'Table'[Total Price])var _total = [Total X Sales]var _result = IF(_category <> "X", DIVIDE(_value, _total, ""), BLANK())return _resultMy "Total Price" column in my data set is alreayd the Qty * Unit Price.My total variable, [Total X Sales] was already being calculated correctly.
- danextian
Super User
Hi TessSA
For each row in your table, this variable should return a non-blank value: var _value = SELECTEDVALUE('Table'[Total Price]). If it returns blank, it could be because _total for those rows return blank. To troubleshoot, return the result of each variable one by one either in the same or separate measures.
Thanks for your guidance. After looking into, it does seem that at the individual row level the measure is calculating correctly but the issues arises for the SKU Category Row Totals when there are multiple rows assigned to one same SKU category, it returns a blank value. I think the issue comes from the fact that the selectedvalue function expects a unique value for the column selected, while in the total I have many of them (under a selected SKU Category there can exist various lines). Maybe I should adapt my measure and use X functions (like sumx) to iterate over each individual row and get the final result needed. I am not sure how to add a X function in my measure, do you have any tips or other ways to solve this issue?
Hi TessSA ,
May I ask if the [Total X Sales] in your expression refers to another measure? Can you share what it is?
Also, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
