Forum Discussion

karo's avatar
karo
Advocate V
1 year ago
Solved

Dynamic Max for X axis for multiple charts

Hi,   Can you help me create a measure to dynamicaly customize Max Value for X axis, please? It should cover all scenarios below.   1. Max Value for all 3 charts should be set up to 275. 2...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    Ahh I didn't know the structure of your data so guessed.

    Highest city =
        CALCULATE(
                MAXX(SELECTCOLUMNS('Table', [Category])
                    ,MAXX(SELECTCOLUMNS('Table',[City]),[sum city sales] ))
                ,all('Table'[Category]))
     
    Change the Inner MAXX to SelectColumn(table,city) that will do the calculation per city instead of per row.
SamWiseOwl's avatar
SamWiseOwl
Super User
1 year ago

Hi karo 

Use a nested MaximumX function.

So for each Category I want the MaxX of their Cities MaxX.

sum city sales = sum('Table'[Sales]) --Base sales measure
Highest city =
    CALCULATE(
            MAXX(SELECTCOLUMNS('Table', [Category])  --Find the highest categories citites sale
                ,MAXX('Table', [sum city sales])) --find the highest cities sales
            ,all('Table'[Category])) --remove the category filter

File attached.

Edit: I agree the above SmallMultiple solution is more scalable!

karo's avatar
karo
Advocate V
1 year ago

Hi SamWiseOwl  Thank you for reply. Please correct me if I am wrong, but your idea returns the value for record with highest Sales per City, not the highest Sales per City as a Sum of Sales per City what I need.

 

    • karo's avatar
      karo
      Advocate V
      1 year ago

      SamWiseOwl yes, I opened it. It was really helpful that you attached the file with the solution. However, please split the 275 in the source table into for example 250 and 25 and then you will see what I mean.

      • SamWiseOwl's avatar
        SamWiseOwl
        Super User
        1 year ago

        Ahh I didn't know the structure of your data so guessed.

        Highest city =
            CALCULATE(
                    MAXX(SELECTCOLUMNS('Table', [Category])
                        ,MAXX(SELECTCOLUMNS('Table',[City]),[sum city sales] ))
                    ,all('Table'[Category]))
         
        Change the Inner MAXX to SelectColumn(table,city) that will do the calculation per city instead of per row.