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. Max Value for all 3 charts should be set up to 75

 

3. Max Value for all 3 charts should be set up to 275.

 

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.

9 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Why not make use of small multiples instead of having multiple visuals?

     

    • karo's avatar
      karo
      Advocate V
      1 year ago

      danextianI tried to use small multiplies, however my goal is to show every category by Sales per City as sorted from highest Sales per City to lowest and small multiples do not allow me to have separate Y Axes.

  • 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.

       

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    You can get the max value across all category / city combinations with

    Max of all categories =
VAR BaseTable =
    ADDCOLUMNS (
        SUMMARIZE ( ALLSELECTED ( Data ), Data[City], Data[Category] ),
        "@sales", CALCULATE ( SUM ( Data[Sales] ) )
    )
VAR Result =
    MAXX ( BaseTable, [@sales] )
RETURN
    Result