Forum Discussion
Getting max value for each row in matrix
Hi,
I have a problem which haunts me for some days now. It didn't look that hard at first, but it turned out challenging.
Overall target:
I have a matrix with 3 dimensions as rows and 1 dimensions as column. I want to make a measure to color these rows. For each row. the lowest value should be green and the highest value should be red.
This has to happen for all 3 dimensions per row.
So i need to figure out the highest (and lowest) value per row and I have to put the value into this perspective. Once this is working properly, the rest should be easy.
Premises:
There are more dimensions like time I want to slice.
And also the dimension which I need for columns I need to slice to.
So the highest/lowest value needs to be evaluated in context of these slicers.
Raw Data as Matrix
Desired result for max value per row
My Approach
I built a test case with only 2 dimensions as rows.
a) Check if calculated value is on row 2 (not aggregated) or 1 aggregated.
b) Use Combination of Summarize and MAX (or MAXA/MAXX) accordingly and get the max value (here only for Row2)
This is where I failed to create a proper formula and I dont want to share my half working formula.
Test Data:
I could share my set of test values, is there any possibility to upload an xlsx here?
| Row1 | Row2 | Row3 | Column | Value | Time |
| A | AA | … | Green | 4 | 1 |
| A | AB | … | Green | 3 | 1 |
| A | … | … | … | 2 | 1 |
| A | AA | … | Yellow | 7 | 1 |
| A | AB | … | Yellow | 8 | 1 |
| A | … | … | … | 10 | 1 |
| A | AA | … | Blue | 2 | 1 |
| A | AB | … | Blue | 4 | 1 |
| A | … | … | … | 8 | 1 |
| B | BA | … | Green | 4 | 1 |
| B | BB | … | Green | 3 | 1 |
| B | … | … | … | 2 | 1 |
| B | BA | … | Yellow | 7 | 1 |
| B | BB | … | Yellow | 8 | 1 |
| B | … | … | … | 10 | 1 |
| B | BA | … | Blue | 2 | 1 |
| B | BB | … | Blue | 4 | 1 |
| B | … | … | … | 8 | 1 |
| C | ... | … | … | … | 1 |
| and so on | |||||
| A | AA | … | Green | 4 | 2 |
| A | AB | … | Green | 3 | 2 |
| A | … | … | … | 2 | 2 |
| A | AA | … | Yellow | 7 | 2 |
| and so on |
Thanks a lot in advance!
Try these measures. The concept is to let the row fields naturally slice the data, but remove column filter context from within the visual (i.e. keep slicer/filter selections). You can use hex codes instead of color names.
Sum Value = SUM ( Test_Table[Value] )Conditional Formatting = VAR vValue = [Sum Value] // create table of all Column values per slicers/filters VAR vColumnValues = CALCULATETABLE ( VALUES ( Test_Table[Column] ), ALLSELECTED ( Test_Table[Column] ) ) VAR vTable = ADDCOLUMNS ( vColumnValues, "@Value", [Sum Value] ) VAR vRowMinValue = MINX ( vTable, [@Value] ) VAR vRowMaxValue = MAXX ( vTable, [@Value] ) VAR vResult = SWITCH ( TRUE, vValue = vRowMinValue, "Green", vValue = vRowMaxValue, "Red" ) RETURN vResultApply conditional formatting using the measure [Conditional Formatting]:
1 Reply
Try these measures. The concept is to let the row fields naturally slice the data, but remove column filter context from within the visual (i.e. keep slicer/filter selections). You can use hex codes instead of color names.
Sum Value = SUM ( Test_Table[Value] )Conditional Formatting = VAR vValue = [Sum Value] // create table of all Column values per slicers/filters VAR vColumnValues = CALCULATETABLE ( VALUES ( Test_Table[Column] ), ALLSELECTED ( Test_Table[Column] ) ) VAR vTable = ADDCOLUMNS ( vColumnValues, "@Value", [Sum Value] ) VAR vRowMinValue = MINX ( vTable, [@Value] ) VAR vRowMaxValue = MAXX ( vTable, [@Value] ) VAR vResult = SWITCH ( TRUE, vValue = vRowMinValue, "Green", vValue = vRowMaxValue, "Red" ) RETURN vResultApply conditional formatting using the measure [Conditional Formatting]: