Forum Discussion
Combination Summarized and ALLSELECTED function not working
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:
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 |
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))returnsumx( 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
3 Replies
- Jihwan_Kim
Super User
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
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))returnsumx( 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
