Calculate max value measure to use as max range in a chart when one column is sliced be two slicers

[Peterkl]

I have a report where I compare two products, lets say A and B. On the left a slicer where product A is selected and a column chart with the amount sold per year and for product B the same slicer and chart. With Edit interactions I make sure that the slicer and graph left don't filter the slicer and graph on the right and vice versa. Product A has as maximal amount of 50 and product B has as maximal amount of 100, how do I get a measure with the max amount selled in a year for both products, 100 in this case so I can use conditional formatting as the max range for the Y axis of both graphs. There are also other products in the table so the measure to get the max value can only check for the mas value of the two filtered products. Normally I am able to make a measure to get the max value but with two slicers on the same tabel I don't get it working.

Here is a link to the pbix file, with tabblad Problem with the problem and two tabblads with expected results.
https://hzpcgroup-my.sharepoint.com/:u:/g/personal/peter_vantklooster_stet-potato_com/EY_12BwrCx5Mr8... 

[v-zhangti · Accepted solution]

Hi, @Peterkl 

You can try the following methods.
New table:

Slicer = VALUES('Table'[Product])

Measure:

Slicer Value = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),[Product]=SELECTEDVALUE(Slicer[Product])&&[Year]=SELECTEDVALUE('Table'[Year])))

Turn the second figure into the one shown.

Measure = 
Var _Sum1=CALCULATE(MAX('Table'[Amount]),ALLEXCEPT('Table','Table'[Product]))
Var _Sum2=MAXX(FILTER(ALL('Table'),[Product]=SELECTEDVALUE(Slicer[Product])),[Slicer Value])
Return
IF(_Sum1>=_Sum2,_Sum1,_Sum2)

Use this measure to set the maximum value of the Y-axis of the first plot.

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Peterkl]

Thanks Charlotte,

I think this will help me. After my holiday I will try it.

Best regards,

Peter