topic Re: Combination Summarized and ALLSELECTED function not working in DAX Commands and Tips

Combination Summarized and ALLSELECTED function not working

Anonymous — Wed, 14 Jul 2021 10:51:39 GMT

Hi all,

I am dealing with the following Challenge. I am trying to determine the number of SKUs within a category that are not reaching the 15% NOS contribution target. It sounds quite easy. However, I want to use an allselected function in de denominator for the NOS contribution so that it will be recalculated if we filter on Category using a slicer. In the 2 screenshots of the report below, the "Below threshold 0.15" is working fine, however the counter is only working for the grand total but it isn't working per category nor per SKU... Apparently, the ALLSELECTED is ignored there and it will return a 100% for the NOS contribution in each line of the table...

I have used the following calculations:

Contribution NOS = DIVIDE(

sum(Sheet1[NOS]),

CALCULATE(sum(Sheet1[NOS]), allselected(Sheet1)))

Below threshold 0.15 = if([Contribution NOS] < 0.15,1,0)

counter =

sumx( SUMMARIZE(Sheet1, Sheet1[SKU], "below threshold", [Below threshold 0.15]), [below threshold]).

It would be really helpful if anyone can share any suggestion how this could be handled. Thank you.

Best regards,

Peter

I have used the following sample data

Category	SKU	NOS
A	1	8
A	2	5
B	3	3
B	4	6
C	5	4
C	6	9

Re: Combination Summarized and ALLSELECTED function not working

Jihwan_Kim — Wed, 14 Jul 2021 12:10:07 GMT

Link to the pbix file

Re: Combination Summarized and ALLSELECTED function not working

Anonymous — Wed, 14 Jul 2021 14:17:13 GMT

Hi Jihwan_kim,
Thanks for your reply. However, it does not solve my issue. The issue that I encounter is that my counter is not working properly. It only works in the row total but it isn't able to count the number of skus below threshold per category. eg, in example1 without filters, it should result in counter = 1 for category A,B and C. and in example2, incl filter category is not A, it should give 1 only for category B

Re: Combination Summarized and ALLSELECTED function not working

Anonymous — Thu, 15 Jul 2021 17:57:33 GMT

Hi all,
I found a solution for my issue. The thing that I had to do is creating a variabe outside the summarize function. In this way, the allsellected function was not ignored anymore since it was fixed.

I used the following DAX formula:

Solution =

var var1 = CALCULATE(sum(Sheet1[NOS]), ALLSELECTED(Sheet1))

return

sumx( SUMMARIZE(Sheet1, Sheet1[SKU], "below threshold",

if(

DIVIDE(

sum(Sheet1[NOS]),

var1)

< 0.15,1,0)), [below threshold])

Hopefully it will help someone that encounters a similar issue