Show one row of data based on a calculated measure in Matrix table

[Anonymous]

Hi All,

I have a matrix table currently looks like this. I want to show the top Brand for each Account, how can I achieve that? Actual and Brand Contribution % are calculated measure. 

Example:

Current Matrix

Expected (show the top Brand Contribution % for each Account Name)

Regards,

Bee Kee

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We can use the Filter on this visual to meet your requirement.

Configure the Brand Filter Type as Top N. Then put the Actual to Value.

The result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[amitchandak]

@Anonymous , Try like

rankx(filter(allselected(Dim_Channel[Customer Channel], Dim_AccountName[Account Name], Dim_Customer_Brand[Brand]), Dim_Channel[Customer Channel] =max(Dim_Channel[Customer Channel])
&& Dim_AccountName[Account Name] = max( Dim_AccountName[Account Name])), [Brand Contribution %],, DESC, DENSE)

[amitchandak]

@Anonymous , Create a column like this and filter on Rank =1 on visual level or use TOPN

rank = rankx(filter(allselected(Table[Customer channel], Table[Account Name], Table[brand]), Table[Customer channel] =max(Table[Customer channel])
&& Table[Account Name] = max( Table[Account Name])), [Brand Contribution %],,desc,dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://youtu.be/DZb_6j6WuZ0
TOPN - https://youtu.be/QIVEFp-QiOk

[Anonymous]

Hi @amitchandak ,

Thanks for the help. I encountered error when I use the code you just showed. Not sure how I can fix this.. Can you please advise? Also, I'm taking the dimensions from separate tables

rankx(filter(allselected(Dim_Channel[Customer Channel], Dim_AccountName[Account Name], Dim_Customer_Brand[Brand]), Dim_Channel[Customer Channel] =max(Dim_Channel[Customer Channel])

&& Dim_AccountName[Account Name] = max( Dim_AccountName[Account Name])), [Brand Contribution %], DESC, DENSE)

Error Message:

Unexpected value for ORDER argument in RANKX function. Use 0/FALSE/DESC for descending order or 1/TRUE/ASC for ascending order.