The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to Solution.
Hi @skytulip33
This measure should work for you where you table is called "Table3"
Max Visitor = CALCULATE(MAX('Table3'[Visitor]),ALL('Table3'))
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.
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?
Hi @skytulip33
Please see the image below where I have taken your data and put it with the measure into Power BI.
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!
Hi @skytulip33
What if you had to try this measure.
Max Visitor = CALCULATE(MAX('Table3'[Visitor]),ALLSELECTED('Table3'[Customer Group]))
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]))
User | Count |
---|---|
109 | |
82 | |
74 | |
53 | |
49 |
User | Count |
---|---|
133 | |
97 | |
94 | |
71 | |
69 |