Matrix Help - Top Sales within each City

[AaronGlenn10]

PBI Rock Stars:  I have a matrix which displays the top 30 cities by sales volumes [see below] -- each city is comprised of a list of restaurants that report TABC sales.  Would like to create a measure that returns the top performing restaurantt for each city based on 'TABC Sales'.  Is this possible?

Thank you so much!  ~AGS

[v-xiaotang · Accepted solution]

Hi @AaronGlenn10 

Measure code:

Measure = 
    var _maxSales =MAXX(ALLEXCEPT('Table','Table'[Region]),CALCULATE(SUM('Table'[TABC Sales]),ALLEXCEPT('Table','Table'[Region],'Table'[Restaurant])))
    var _restaurant= CALCULATE(SELECTEDVALUE('Table'[Restaurant]),FILTER(ALLEXCEPT('Table','Table'[Region]),CALCULATE(SUM('Table'[TABC Sales]),ALLEXCEPT('Table','Table'[Region],'Table'[Restaurant]))=_maxSales))
return IF(ISINSCOPE('Table'[Restaurant])||NOT(HASONEFILTER('Table'[Region])),BLANK(),_restaurant)

Best Regards,

Community Support Team _Tang

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

[negi007]

@AaronGlenn10 

you can prevent interaction between two visuals using edit interactions then blocking it

[negi007]

@AaronGlenn10  in this case you can create a column to filter out max value for each region like below

MaxValue =

if (CALCULATE ( MAX ( 'Table'[tabc sales] ), ALLEXCEPT ( 'Table', 'Table'[region] ) )='Table'[tabc sales]

, "top","others")

then in your visual keep the top from MaxValue filter applied so that only top values are visible in matrix

[AaronGlenn10]

Thank you -- the measure appears to be overriding the 'Top Filter' I have applied in the Matrix Visual.  Is there a way to prevent?