How to calculate the occurrence of top N in a measure.

[lilynguyen]

I have a data like this

Dept	Count of bill	Date
A	50	1/1/2020
B	250	1/1/2020
C	15	1/1/2020
D	30	1/1/2020
E	40	1/1/2020
A	20	2/2/2020
B	15	2/2/2020
C	24	2/2/2020
D	90	2/2/2020
E	11	2/2/2020

So, I will create a measure to rank the Dept by column Count of bill by Date, I have the data like this:

Dept	Count of bill	Date	Rank
A	50	1/1/2020	4
B	250	1/1/2020	5
C	15	1/1/2020	1
D	30	1/1/2020	2
E	40	1/1/2020	3
A	20	2/2/2020	3
B	15	2/2/2020	2
C	24	2/2/2020	4
D	90	2/2/2020	5
E	11	2/2/2020	1

Now, I would like to have the frequency of Debt which in top 3 by date, like this:

DEPT	Occurrence
A	1
B	1
C	1
D	1
E	2

Any ideas from you guys?
Thanks so much

[lilynguyen]

Tks so much for both of you guys. Both solutions works

[sanalytics]

@lilynguyen 

Also you can follow below pattern

attached is the pbix file

https://dropfiles.org/lBqAHOqz

Regards,

sanalytics

if it is your solution please like and accept it as your solution

[mahoneypat · Accepted solution]

Please try this measure expression in a table visual with your Dept column, replacing Bills and Rank Measure with the actual names.

Occurrence =
VAR vThisDept =
    MIN ( Bills[Dept] )
VAR vSummary =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                Bills,
                Bills[Dept],
                Bills[Date]
            ),
            "cRank", [Rank Measure]
        ),
        ALLSELECTED ( Bills )
    )
RETURN
    COUNTROWS (
        FILTER (
            FILTER (
                vSummary,
                Bills[Dept] = vThisDept
            ),
            [cRank] <= 3
        )
    )

Regards,

Pat