How to create a measure to show the max value of another filtered measure

[skytulip33]

I have create a matrix with two columns, the column names are "year" and "visitor". "Visitor" is actually a measure filtered by a date type measure "Year". The table structure is like this:

Year	Visitor
2017	260
2016	220
2015	230
2014	210

What I would like to do is to add another column into this matrix. The third column is named "MaxVisitor". I would like to have this column to always display the Maximum value of the "Visitor" measure, the way I would like it to display is like this:

Year	Visitor	MaxVisitor
2017	240	260
2016	260	260
2015	230	260
2014	210	260

as you can see that the third column "MaxVisitor" always displays 260 which is the largest value from the column (i.e the filtered measure) "Visitor".

I am not sure if this can be done?

Any advice will be appreciated!

[GilbertQ]

Hi @skytulip33

This measure should work for you where you table is called "Table3"

Max Visitor = CALCULATE(MAX('Table3'[Visitor]),ALL('Table3'))

[abhilash2612]

What if Visitor is a measure created and not a loaded column, you cant use MAX fucntion as it wil give you error: could not be found
I tried using MAXX but still have issues

please guide

Thanks

Abhilash.

[skytulip33]

Hi @GilbertQ,

Thanks for your reply. I tried your solution but it seemed that it doesn't work.

The value calculcated from your formula seems to be the SUM of all visitors rather than the Max visitors filtered by the Year? It seemed that the "year" filter is ignored by that formula?

[GilbertQ]

Hi @skytulip33

Please see the image below where I have taken your data and put it with the measure into Power BI.

[skytulip33]

@GilbertQ

Thank you for that. I tried again and realized that I missed sth. in my description. I didn't realize that it would cause this difference before. 

I need to modify my description, that is, what if the customers are divided into two goups? say the original data is like:

Year	Visitor	Customer Group
2017	260	1
2016	220	1
2015	230	1
2014	210	1
2017	265	2
2016	262	2
2015	158	2
2014	222	2

and when I tried to use your solution, it seemed that the MaxVisitor will be incorrect for Group 1. As can be seen in the pic:

Do you mind giving some suggestion on this?

Thank you!

[GilbertQ]

Hi @skytulip33 

What if you had to try this measure.

Max Visitor = CALCULATE(MAX('Table3'[Visitor]),ALLSELECTED('Table3'[Customer Group]))

[skytulip33]

@GilbertQ

Thanks for your reply. I did a bit of test and modified a bit on your suggested solution, the following one works fine:

MaxVisitor = calculate(max(Table3[Visitor]),ALLSELECTED(Table3[Year]))   

[GilbertQ · Accepted solution]

That's correct. I totally forgot that it should have used the year instead of Customer Group. Well done on figuring it out

[skytulip33]

@GilbertQ

Thank you for your help!

[GilbertQ]

Glad to assist you