Top N categories within selected date but display values monthly even if other category is larger

[mati_gouda]

Hello,

I tried every possible option I was able to find and somehow still does have some issues and errors. 
Case scenario is: we have a table with Month Year (like FEB-2024), some sub-category and simple number of cases. Like below

I have a slicer on the page with dates, so assume that we have selected dates: 01/02/2024–01/03/2024. 
What I want to do is take the 3 subcategories with the highest number of cases within those dates (so in those two months). SubCategories are Test Date Transfer, Certificate Enquiry and Payment.

And I want to show only them and their values on a table (or line chart or whatever) even though those values may not be the highest per month (like Payment Sub Category → it's in the top 3 in February but not in March - but that does not matter because in the whole period it's in the top 3). 

So essentially I want to take top 3 subCategories for the whole period and display only those SubCategories month by month (and it's values of course) even thought they may not be in top categories per one month. 

I wrote the measure, which I understand, but I have an impression that ALLSELECTED on dates behaves weirdly (while ALLSELECTED on categories seems to be correct). 

Case Count by Sub Category RANK TEST =
VAR _rank =
    RANKX (
        CALCULATETABLE (
            VALUES ( Dataset_Cases[SubCategory] ),
            ALLSELECTED ( Dataset_Cases[SubCategory] ),
            ALLSELECTED ( Calendar_Table[Month & Year - short] ),
            ALLSELECTED ( Dataset_Cases[DateOpened] )
        ),
        CALCULATE ( COUNT ( Dataset_Cases[CaseID] ) ),
        ,
        DESC,
        DENSE
    )
RETURN
    _rank

[Anonymous · Accepted solution]

Hi @mati_gouda 

Please try this:

Here I create a set of sample for your reference:

Then add a calculate column to rank the values:

Rank =
RANKX (
    ALL ( 'Table' ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sub Category] = EARLIER ( 'Table'[Sub Category] )
        )
    ),
    ,
    DESC,
    DENSE
)

Next, create a measure:

SumValue =
VAR _currentRank =
    MAX ( 'Table'[Rank] )
RETURN
    IF (
        _currentRank <= 3,
        SUMX (
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Rank] = _currentRank ),
            'Table'[Value]
        )
    )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.