RANKX based in measure with date filters

[Remon1991]

Hello everybody,

I have a problem which i can't solve. I'm trying to create 5 diagrams from the top 5 of hours from last 13 weeks. 
Where every one has is one diagram with dynamic title and where you can see how the hours are spread out over the last 13 weeks.

I have a filter on my dashboard from year and week. So if i select week 3 from 2025 then it shows me data from:

week 43 from 2024 up to and including week 3 from 2025. 

The measure that is use is to get the right hours is:

Laatste 13 weken uren ongepland =

    CALCULATE(

    SUM('OEE trend'[Uren]),

        FILTER(

        'OEE trend',

        ([Date]>='Date'[Laatste 13 weken]

        &&[Date]<='Date'[Laatste week]

        &&[Category 2]= "Ongepland"

)))

 I also use almost same measure for the frequency. See diagram below for the result of these measures.

So the highest value is 46 hours from activity Fels 127mm. I want to see from this top 5 how it is spreaded over last 13 weeks every one in a seperated diagram with a dynamic title. See below example from the highest value. (it shows 8 weeks data, because some weeks has no data).

I can create this diagram, because i use the topn filter for activity based on last 13 weeks hours.

You can't use this filter to see only the second hightest etc. Then i see highest and second highste together. 

 I think rankx gives me the solution that i can filter the 5 diagrams on number 1, 2, 3, 4 and 5. But maby there is another solution to get the 5 diagrams like i want?

[Anonymous]

Hi @Remon1991 ,

Based on the information, creating a ranking measure that ranks the activities based on the hours.

Activity Ranking =
  VAR CurrentActivity = SELECTEDVALUE('OEE trend'[Activity])
  RETURN
   RANKX(
    ALLSELECTED('OEE trend'[Activity]),
    [Laatste 13 weken uren ongepland],
    ,
    DESC,
    DENSE
   )

Then, creating the visual filter for each diagram to the top 5 visual.

Top1Hours =
CALCULATE(
    SUM('OEE trend'[Uren]),
    FILTER(
        'OEE trend',
        [Activity Ranking] = 1
    )
)

Top1Hours =
CALCULATE(
    SUM('OEE trend'[Uren]),
    FILTER(
        'OEE trend',
        [Activity Ranking] = 2
    )
)

Best Regards,

Wisdom Wu

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

[Rupak_bi · Accepted solution]

you can explore offset as well. both may work based on how you are building the data models and summarization from the raw data.

[Remon1991]

I have created this measure 4 times for 2,3,4 and 5. And this measure works when i filter the activity on topn 1 based below measure.

Laatste 13 weken uren ongepland 2de =

CALCULATE(

    [Laatste 13 weken uren ongepland],

    OFFSET(

        1,

        ALLSELECTED('OEE trend'[Activity]),

        ORDERBY('OEE trend'[Laatste 13 weken uren ongepland],ASC)

    )

)