Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I have a data set that has values and the year that value was recorded. I want to create a measure that will display the maximum value for that year when I add it to a table. For example, my data looks like this:
Year | Value |
2008 | 1 |
2008 | 3 |
2008 | 1 |
2009 | 4 |
2010 | 3 |
2010 | 5 |
2010 | 1 |
2011 | 3 |
2011 | 2 |
2012 | 1 |
So the measure I want would add a column to the table that looks like this:
Year | Value | Max |
2008 | 1 | 3 |
2008 | 3 | 3 |
2008 | 1 | 3 |
2009 | 4 | 4 |
2010 | 3 | 5 |
2010 | 5 | 5 |
2010 | 1 | 5 |
2011 | 3 | 3 |
2011 | 2 | 3 |
2012 | 1 | 1 |
I tried:
Solved! Go to Solution.
@Anonymous ,
try like
Measure = CALCULATE(MAX('Table'[Value]),allexcept('Table'[Year]))
You can also try
@Anonymous ,
try like
Measure = CALCULATE(MAX('Table'[Value]),allexcept('Table'[Year]))
Thank you, that works.
What if I want the average for each year?
Year | Value | Average |
2008 | 1 | 1.66 |
2008 | 3 | 1.66 |
2008 | 1 | 1.66 |
2009 | 4 | 4 |
2010 | 3 | 3 |
2010 | 5 | 3 |
2010 | 1 | 3 |
2011 | 3 | 2.5 |
2011 | 2 | 2.5 |
2012 | 1 | 1 |
HI @Anonymous,
You can try below measure formula if it works:
Measure =
CALCULATE (
AVERAGE ( table[Value] ),
ALLSELECTED ( table ),
VALUES ( table[Year] )
)
You can change the aggregate functions to apply different summary mode on your fields. (min, max, sum, average...)
Regards,
Xiaoxin Sheng
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
61 | |
55 |
User | Count |
---|---|
171 | |
109 | |
105 | |
73 | |
71 |