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.
I have seen a lot of MIN and MAX formulas out there but they do not seem to work with the data I have. I have a table called BR Overhead which holds individual value fields, such as below
I cannot for the life of me how to write a formula to highlight the MIN and MAX in this table.
Any suggestions?
Solved! Go to Solution.
@dedelman_clng @varshahrame If you really don't want to unpivot your data, you can create a measure to find the the Max Value, which I think you have done, then create a measure per column that will check to see if it's the Max Value and then use that to conditionally format the background or however you want to format it.
I've attached the PBIX as well. Hope this helps.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
You need to unpivot the table in Power Query to get the Min/Max
Proud to be a Super User!
Paul on Linkedin.
@varshahrame - unfortunately, conditional formatting like you are going for operates on a column, not on a row, at least to my knowledge. If you were to "transpose" the table to be a list of attributes and values, then it may be possible, but Pivot/Transpose is not available in DAX (again, to my knowledge).
I'll see if I can bring others into this thread that may have ideas.
David
@dedelman_clng @varshahrame If you really don't want to unpivot your data, you can create a measure to find the the Max Value, which I think you have done, then create a measure per column that will check to see if it's the Max Value and then use that to conditionally format the background or however you want to format it.
I've attached the PBIX as well. Hope this helps.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@DataZoe Are you able to do a MIN and MAX on the same conditional formatting? I added in my minimum measures and I looked at the conditional formatting and I dont see where you can add the minimum as well as the maximum?
@varshahrame Of course, I've attached the PBIX and modified it like so:
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
Thank you so much @DataZoe! This worked perfectly. The only issue is having to do this for ALL other columns! haha....but it works, so I am happy about that.
APPRECIATE EVERYONES HELP!
That is what I was afraid of, everything I had been reading was talking about columns as opposed to rows and then I did come across a few articles that did say to transpose the data in order to do the conditional formatting.
Thank you!
You need to put an aggregation on each field. If you're just looking at a single row each time, you can use SUM and it will give you the same value.
MAX( MAX( SUM(ColA), SUM(ColB) ), SUM(ColC) ) etc
Yayyyy thank you so much!!!! On to my next question...from the detail below, how can i have the field that has that max value highlighted. So in this example I would like the field 'RENT" to be highlighted as thats the MAX value.
I truly appreciate your help. I am so new to Power BI and I am still finding my way.
So I did try this and it allowed me to choose my first field but it wouldnt let me select the 2nd field....it was showing as grayed out.
Hi @varshahrame -
It may be messy, but you'll probably have to nest MAX/MIN for each field you want compared
MAX( MAX( MAX( MAX( DEPR_AMOUNT, INS_BLDG), MAG), PHONE), REP_MAINT_EQ)
etc
Hope this helps
David
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |