Counting Items in Group Derived from Ranking

[ElliottBowles]

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

Data Sample (CSV) 

[ElliottBowles · Accepted solution]

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
    ))