Top 5 values in category for selected year and all years

[AnthonyJoseph]

Hello Community,

I have a quiet a complicated task (I feel so) but I'll try to explain as detail as possible. Basically, I have two tables (Data and Invoice). Data table contains the Entity name and Year. Invoice table has invocie details with descriptions  for the entities and their correcponding years. I have also included the data here

The ask is below:

1) I need to create two visuals one to display top 5 Category (based on Invoice values) for all entities for a selected year.

2) One visual to display the Invoice value for the above identifies top 5 category across all years.

When the user selects the year from the drop down then:

1) The Entities that are in the selected year and their Top 5 "Category" based on the Invoice values should be calculated.

2) In the second visual, we should see the top 5 category (based on the Invoice value) of the selected year and also the invoice value for the other years for those top 5 categories from the selected year.

For example:

The selected year is 2022 and The entities A,C and D have data for 2022 then visual should sum up all the Invoice values for all the entities (A,C,D) and display the top 5 categories.

Top 5 categories for 2022

Here the top 5 categories are (Social, Entertainment, Mathematics, Special preparation and Science).

Top 5 categories in 2022 Vs other years (2020 and 2021):

Here the top 5 categories from 2022 are picked and their 2021 and 2020 values are displayed for Entites A,C and D (Category: Social, Entertainment, Mathematics, Special preparation and Science).

Hope this is explains the request, please if someone can help here that would be really helpful

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

[AnthonyJoseph]

Thank you so much @Jihwan_Kim. The solution worked as detailed but one missing thing is when I unselected 2022 I could still see data for 2022. Basically, when I unselect 2022 from the slicer, I want the second visual to display the top 5 categories for 2021 (the latest selected year) and their corresponding Invoice values for 2020 i.e. the other selected year values... Is there a possibility to achieve this ?

[Jihwan_Kim]

Hi,

thank you for your message.

Please check the attached file, and I amended some measures in order to meet your requirement.

Please kindly check if it works.

Thank you.

[AnthonyJoseph]

Thanks @Jihwan_Kim Sorry but I still see 2022 even if its not selected...

[Jihwan_Kim · Accepted solution]

Hi,

Could you please check the attached file?

The previous measure was only for selecting one year.

I amended, and please check if it works.

Thank you.