cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Matrix Help - Top Sales within each City

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

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Community Champion

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

Proud to be a Super User!

Community Champion

@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

Proud to be a Super User!

Helper III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors