cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Counting Items in Group Derived from Ranking

Hi everyone,

I have been tasked with producing a 'quintile report'. Given a set of sales data, we want to rank by a sales metric, then group the items into equally sized quintiles. From there, we'd like to count by manufacturer how many of their items appear in each group, and display the result on a 100% stacked bar visual.

I have made progress with this by creating a helper table with the quintile minimums and maximum values (0, 0.2, 0.4, etc.). I then used the below dax formula to count / group by the ranking (taking the rank divided by the total items to determine which group it should be in):

`Item Count by Quintile = CALCULATE(COUNT('Quintile Data'[ITEM]),    FILTER(        VALUES('Quintile Data'[ITEM]),         COUNTROWS(            FILTER('Quintile Groups',                DIVIDE(                    RANKX(ALLSELECTED('Quintile Data'[ITEM]),[Dollar Velocity],,DESC),                    COUNTX(ALLSELECTED('Quintile Data'[ITEM]),COUNT('Quintile Data'[ITEM])),""                ) > 'Quintile Groups'[Min]            && DIVIDE(                    RANKX(ALLSELECTED('Quintile Data'[ITEM]),[Dollar Velocity],,DESC),                    COUNTX(ALLSELECTED('Quintile Data'[ITEM]),COUNT('Quintile Data'[ITEM])),""                ) <= 'Quintile Groups'[Max] )        ) > 0    ))`

This allows us to split the data in equally sized groups, and it works great if viewing the stacked chart at a total level. However, if I bring the manufacturer into the visual on the x axis, the above calculation appears to be performed at each manufacturer's level.

I had initially tried using ALL() instead of ALLSELECTED(), but I do have a filter in the visual (Market) which I need to include in the calculation. My dax skills show their limits when filter contexts get involved, so hoping someone might be able to help with finding a way to include the Market filter but exclude the Manufacturer in the above. Perhaps its a use case for ALLEXECEPT()?

I have attached a sample data file and PBIX showing what I'm trying to do. In the PBIX, the bottom chart is an example of the final form, but it is calculating at the manufacturer level currently.

Thank you!

Sample PBIX

1 ACCEPTED SOLUTION
Frequent Visitor

So after posting this I did some more research and education on filter contexts and the various ALL... functions.

I realized I need to use an ALLEXCEPT function to remove all filters except for the Market filter:

`ALLEXCEPT(Data, Data[Market])`

Initially that didn't work, but I remembered watching a video where they needed to wrap measures in a CALCULATE to get them to work at the right level of detail. I'm still a little fuzzy on what that does but it worked! I wrapped the [Sales Measure] and COUNT(Data[Product]) measures with a a CALCULATE, and voila! This is my final formula in case it helps anyone:

`Item Count = CALCULATE (    COUNT ( Data[Product] ),    FILTER (        VALUES ( Data[Product] ),        COUNTROWS (            FILTER (                'Quintile Groups',                DIVIDE (                    RANKX ( ALLEXCEPT (Data,Data[Market]), CALCULATE([Sales Measure]),, DESC ),                    COUNTX ( ALLEXCEPT (Data,Data[Market]), CALCULATE(COUNT ( Data[Product] )) ),                    ""                ) > 'Quintile Groups'[Min]                    && DIVIDE (                        RANKX ( ALLEXCEPT (Data,Data[Market]), CALCULATE([Sales Measure]),, DESC ),                        COUNTX ( ALLEXCEPT (Data,Data[Market]), CALCULATE(COUNT ( Data[Product] )) ),                        ""                    ) <= 'Quintile Groups'[Max]             )        ) > 0    ))`

Frequent Visitor

So after posting this I did some more research and education on filter contexts and the various ALL... functions.

I realized I need to use an ALLEXCEPT function to remove all filters except for the Market filter:

`ALLEXCEPT(Data, Data[Market])`

Initially that didn't work, but I remembered watching a video where they needed to wrap measures in a CALCULATE to get them to work at the right level of detail. I'm still a little fuzzy on what that does but it worked! I wrapped the [Sales Measure] and COUNT(Data[Product]) measures with a a CALCULATE, and voila! This is my final formula in case it helps anyone:

`Item Count = CALCULATE (    COUNT ( Data[Product] ),    FILTER (        VALUES ( Data[Product] ),        COUNTROWS (            FILTER (                'Quintile Groups',                DIVIDE (                    RANKX ( ALLEXCEPT (Data,Data[Market]), CALCULATE([Sales Measure]),, DESC ),                    COUNTX ( ALLEXCEPT (Data,Data[Market]), CALCULATE(COUNT ( Data[Product] )) ),                    ""                ) > 'Quintile Groups'[Min]                    && DIVIDE (                        RANKX ( ALLEXCEPT (Data,Data[Market]), CALCULATE([Sales Measure]),, DESC ),                        COUNTX ( ALLEXCEPT (Data,Data[Market]), CALCULATE(COUNT ( Data[Product] )) ),                        ""                    ) <= 'Quintile Groups'[Max]             )        ) > 0    ))`

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.