Display only certain rows of a matrix visual based on a column's rank with DAX

[sim_kay7]

Hello,

I have a matrix visual showing costs and turnover for different products for 2020 and 2021.

I also created measure to get the rank of a product in terms of costs and turnover.

I'm trying to find a way to display only those rows of products where costs in 2021 are within the top3 (without using the filter pane).

Can anybody give a hint how to do it?

Cheers

Simkay

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

_Costs: =

VAR yearselect = 2021

VAR newtable =

FILTER ( ALL ( Data ), Data[Year] = yearselect )

VAR topthreecost =

TOPN ( 3, newtable, Data[Costs], DESC )

VAR withoutyearfilter =

SUMMARIZE ( topthreecost, Data[Product] )

RETURN

CALCULATE (

SUM ( Data[Costs] ),

KEEPFILTERS ( ( TREATAS ( withoutyearfilter, Data[Product] ) ) )

)

_Turnover: =

VAR yearselect = 2021

VAR newtable =

FILTER ( ALL ( Data ), Data[Year] = yearselect )

VAR topthreecost =

TOPN ( 3, newtable, Data[Costs], DESC )

VAR withoutyearfilter =

SUMMARIZE ( topthreecost, Data[Product] )

RETURN

CALCULATE (

SUM ( Data[Turnover] ),

KEEPFILTERS ( ( TREATAS ( withoutyearfilter, Data[Product] ) ) )

)

[sim_kay7]

Thanks @Jihwan_Kim, that's almost what I wanted. However when I apply it to my dataset it doesn't show the top3 consistently. I assume this is because in my dataset there can be multiple rows with the same product per year. I don't seem to be able to attach .pbix here, so here is a picture of my data:

How can I modify your code to make it work on this data as well?

Best Regards

Simkay

[sim_kay7 · Accepted solution]

@Jihwan_Kim I added an intermediate step to summarize the costs per product per year.

Now it works. Thanks for your help!

Here is the full solution:

Cheers, Simkay

[VahidDM]

Hi @sim_kay7 

You can create another table with DAX and use that or create measure for those items and filter them to just show the top 3, then add measure as a value.

Share you PBIX file or Sample of data to be able to work on it.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/