Dax Max of 3 Columns and Average if all years selected

[Anonymous]

Hello Power BI Community, I am new. If someone could please help me.

I have 3 columns, I want to be able to sum the columns and for the formula to return to max of the 3 columns in table below return me the highlighted yellow number. And if I select all years on the filter for it to find the average, but if I select the year it return the max of all 3 columns. 

So for example if I have a table that is filtered by 2022 returns 22, if it is filtered by 2023 it returns 23, and if there is no filter it returns the average of 22 and 23 22.5.

This code provides me with the max of the 3 columns (that I turned into a measure), but it totals the years when I want it to average the years example the diagram 22.5 not 45.

MaxOfThreeMeasures =
VAR MaxValue = MAX ( Measure1, Measure2 )
RETURN
MAXX ( VALUES ( Table ), MAXX ( VALUES ( Table ), MAXX ( VALUES ( Table ), MAX ( MaxValue, Measure3 ) ) ) )

Anyone able to help please?

[Dangar332 · Accepted solution]

Hi, @Anonymous 

try below code for measure

Measure 2 = 
var a_s = SUM(alisia[Column1])
var b_s = SUM(alisia[Column2])
var c_s = SUM(alisia[Column3])
var a_v = AVERAGEX(alisia,CALCULATE(SUM(alisia[Column1]),ALLEXCEPT(alisia,alisia[year])))
var b_v = AVERAGEX(alisia,CALCULATE(SUM(alisia[Column2]),ALLEXCEPT(alisia,alisia[year])))
var c_v = AVERAGEX(alisia,CALCULATE(SUM(alisia[Column3]),ALLEXCEPT(alisia,alisia[year])))
var sum_ =MAX(MAX(a_s,b_s),c_s)
var avg_ = MAX(MAX(a_v,b_v),c_v)
RETURN
IF(ISFILTERED(alisia[year]),sum_,avg_)

if any year not selected then like below

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

[Anonymous]

Hi @Dangar332  THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[gmsamborn]

Hi @Anonymous 

I would like to help but I'm a little unclear about your requirements.

Can you provide the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

A .pbix file with sample data would be best.

[Anonymous]

Hi @gmsamborn , I am unable to past a link but I can provide screenshots hopefully they can clarify.

Here is the data set:

Here is the card, which is saying 45.0 but I want it to say 22.5 and average not sum when its on select all (2022 + 2023)

If I click on a year its correct because I want it to sum the 3 columns and provide me the max. The sum is 22 for 2022.

And 23 for 2023

So the sum is 45 which its giving now, but I want it to give me 22.5 the average of the two.