Forum Discussion

AaronGlenn10's avatar
AaronGlenn10
Icon for Helper III rankHelper III
4 years ago
Solved

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

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

4 Replies

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      AaronGlenn10
      Icon for Helper III rankHelper III
      4 years ago

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

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    4 years ago

    AaronGlenn10 

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

     

     

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.