Dynamic Max for X axis for multiple charts

[karo]

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.

[johnt75]

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

[SamWiseOwl]

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]

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.

[SamWiseOwl]

Hi @karo 

Did you open the attached file?
It appears to work for each given scenario
275 for all regions

region 1: 75

Region 2: 275

[karo]

@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 · Accepted solution]

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.

[karo]

Thank you ❤️

[danextian]

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

[karo]

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