Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to get max value in column chart

Hi

 

I uploaded a file to dropbox

 

https://www.dropbox.com/scl/fi/bjq8hmi01cumhcr151b0l/test.pbix?rlkey=zlp36uco7jc8mth4417b38p4b&dl=0 

 

Here you see a colum chart, with 2 slicers : Region and Shipmode
There is a filter on the visual, Year = 2016 (to simplify it)

 

I created a measure 'Max Orders 3' which I refer to in measure 'Dynamic Display Max'

'Dynamic Display Max' is used in conditional formatting of the columns of the column chart

 

I want to give the column chart with the highest value another color, blue.

But this only works if I select one item from slicer Region and one from slicer Shipmode.

But I also want it to work when I select mulitple items from the slicers.

 

How do I get it working?

 

Thnx for the help

Ron

 

P.S. When you use the Legend in the column chart, you can't conditional format the columns anymore. That's a pity

 

VahidDM eliasayyy , tamerj1 

  • TomasAndersson's avatar
    TomasAndersson
    2 years ago

    Hi!
    In your "Max Orders Europe 3", remove Orders[Region] and Orders[ShipMode] from summarize so that the table is only grouped by quarter and not by those columns as well. This allows mulitple items to be selected.

    Max Orders Europe 3 modified = 
VAR _table = 
CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(Orders,
        Dates[Year], Dates[QuarterOfYear]
        ),
        "Totaal",[Sum Orders]
    ),
    ALLSELECTED(Orders)
)
VAR MaxVal = MAXX(_table,[Totaal])
RETURN MaxVal


    You should also be able to remove ADDCOLUMNS() and make the measure a bit shorter, but maybe you prefer it that way:

    Max Orders Europe 3 short = 
VAR _table = 
CALCULATETABLE(
    SUMMARIZE(
        Orders,
        Dates[Year], Dates[QuarterOfYear],
        "Totaal",[Sum Orders]
        ),
    ALLSELECTED(Orders)
)
VAR MaxVal = MAXX(_table,[Totaal])
RETURN MaxVal



    Hope this helps!

     

2 Replies

  • TomasAndersson's avatar
    TomasAndersson
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi!
    In your "Max Orders Europe 3", remove Orders[Region] and Orders[ShipMode] from summarize so that the table is only grouped by quarter and not by those columns as well. This allows mulitple items to be selected.

    Max Orders Europe 3 modified = 
VAR _table = 
CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(Orders,
        Dates[Year], Dates[QuarterOfYear]
        ),
        "Totaal",[Sum Orders]
    ),
    ALLSELECTED(Orders)
)
VAR MaxVal = MAXX(_table,[Totaal])
RETURN MaxVal


    You should also be able to remove ADDCOLUMNS() and make the measure a bit shorter, but maybe you prefer it that way:

    Max Orders Europe 3 short = 
VAR _table = 
CALCULATETABLE(
    SUMMARIZE(
        Orders,
        Dates[Year], Dates[QuarterOfYear],
        "Totaal",[Sum Orders]
        ),
    ALLSELECTED(Orders)
)
VAR MaxVal = MAXX(_table,[Totaal])
RETURN MaxVal



    Hope this helps!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi TomasAndersson thnx for helping. It works. I made a mistake in thinking :(.
      Still learning DAX, bit by bit. Once again, thnx