Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone, I am new to power bi and trying to build a measure that will allow me to sum the total value by month dynamically and return the max value for that person. Can anyone help, please!
I am currently using this formula to get the sum by month, but I am not sure how to get the max value based on the sum by month.
=
Here is a sample of data
Names | Values | MonthYear |
James | 150 | Jan-19 |
James | 200 | Jan-19 |
James | 200 | May-19 |
James | 125 | Jun-19 |
James | 175 | Jun-19 |
Gaby | 75 | May-19 |
Gaby | 300 | May-19 |
Gaby | 350 | Sep-19 |
Gaby | 250 | Sep-19 |
Gaby | 225 | Sep-19 |
Gaby | 125 | Apr-19 |
Gaby | 150 | Apr-19 |
Gaby | 275 | Apr-19 |
Gaby | 350 | Feb-19 |
The results I am looking for are:
James - 350
Gaby - 825
Also, Is there a way to put this into a gauge visual as the maximum?
Solved! Go to Solution.
Hi @Jarodp42 ,
You can use this measure and put it into a table visual together with the names: (I called my table Names btw):
Measure =
VAR _groupedTable = SUMMARIZE(Names, Names[MonthYear], "SUM", SUM(Names[Values]))
RETURN
MAXX(_groupedTable, [SUM])
Result:
You can put this measure into the Gauge target lable, but remember that it will be evaluated for the current data context. So, with your sample data, it will evaluate to 850 but not because Gaby has the highest, but because the month Sept-19 is overall the highest.
let me know if this answers your question 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Create a Max column like the one you have. (Mine was this)
Hi @Jarodp42 ,
You can use this measure and put it into a table visual together with the names: (I called my table Names btw):
Measure =
VAR _groupedTable = SUMMARIZE(Names, Names[MonthYear], "SUM", SUM(Names[Values]))
RETURN
MAXX(_groupedTable, [SUM])
Result:
You can put this measure into the Gauge target lable, but remember that it will be evaluated for the current data context. So, with your sample data, it will evaluate to 850 but not because Gaby has the highest, but because the month Sept-19 is overall the highest.
let me know if this answers your question 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
@JarroVGIT Thank you for your response. I was having to build it in two steps but your measure allowed me to get my desired outcome in one measure! As for the Guage visual that makes sense as it wouldn't know which name to filter for. Please correct me if I am wrong, but I could throw a page filter on for names and when selecting certain names it should give me their max value by month, right? Thanks again for helping with my issue!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |