Forum Discussion

ElliottBowles's avatar
ElliottBowles
Frequent Visitor
4 years ago
Solved

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...
BINS
DAX
grouping
ranking
  • ElliottBowles's avatar
    ElliottBowles
    4 years ago

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

ElliottBowles's avatar
ElliottBowles
Frequent Visitor
4 years ago

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