Forum Discussion
DAX with high granularity
In my work I have been asked to migrate some formulas from excel to DAX, the calculations that are made in excel are at the SKU level, so they have a high granularity, I am looking to calculate a share, the calculation gives me correct results as long as the visual objects have a high granularity, otherwise my result is always 100%. How can I get the participation calculated taking into account only the selected category.
My DAX is
peso_sku_act = DIVIDE(SUMX(Worksheet,[ACT Volume]),CALCULATE(SUM(Worksheet[ACT Volume]),ALLSELECTED(Worksheet)))
example visuals
WRONG SIGHT - CURRENT RESULT
CORRECT VIEW - EXPECTED RESULT
Excel_Sample : Sample URL
Does anyone know how I can get the expected result or have any idea how I can adjust my code to get the expected result.
Hi mlozano ,
Please share a sample data that we can copy-paste - not an image.
Here's how the community can help you faster. https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump-to/first-unread-messageI downloaded the excel file and did the measure as below
peso_sku_act = var _num = SUM(Worksheet[ACT Volume]) var _den = CALCULATE (SUM(Worksheet[ACT Volume]), all(Worksheet)) RETURN DIVIDE(_num, _den, 0)I see what went wrong, in my view ...
a) when you use ALLSELECTED (Worksheet), it uses the current context and filters. so, when you are doing only for Core Price Segment, it shows as 100% but when you have all price segments, it shows as 72.25%
b) I change the formula to use all (worksheet), which uses the whole data for calculating the denominator percentage ...so it shows as 72.25%
Hope this helps!
8 Replies
Hi mlozano ,
Please share a sample data that we can copy-paste - not an image.
Here's how the community can help you faster. https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump-to/first-unread-messageWithout having the sample data and example, tought to answer, but trying purely reviewing your DAX
peso_sku_act = var _num = SUM(Worksheet[ACT Volume]) var _den = CALCULATE (SUM(Worksheet[ACT Volume]),ALLSELECTED(Worksheet)) RETURN DIVIDE(_num, _den, 0)I already tried it with that code but the drawback is that the participation must be calculated at the SKU level, which would be the lowest granularity and when filtering from some visual object, the result of the price of the selected segment is always 100%, and for example , in the case of CORE, its participation is 72% and when filtering, 100% is shown.
I downloaded the excel file and did the measure as below
peso_sku_act = var _num = SUM(Worksheet[ACT Volume]) var _den = CALCULATE (SUM(Worksheet[ACT Volume]), all(Worksheet)) RETURN DIVIDE(_num, _den, 0)I see what went wrong, in my view ...
a) when you use ALLSELECTED (Worksheet), it uses the current context and filters. so, when you are doing only for Core Price Segment, it shows as 100% but when you have all price segments, it shows as 72.25%
b) I change the formula to use all (worksheet), which uses the whole data for calculating the denominator percentage ...so it shows as 72.25%
Hope this helps!
