Forum Discussion
Color only highest and lowest values
Hi Daniel_Fdrvc
Below is the output you'll get by applying the steps in the PBIX attached:
You need to create the following two measures:
Avg =
VAR _1 = CALCULATE ( AVERAGE ('Table'[Amount] ) , DATESMTD ('Table'[Date] ) )
RETURN
_1Background Avg =
VAR _1 = MINX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg] )
VAR _2 = MAXX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg] )
RETURN
SWITCH ( TRUE() , [Avg] = _1 , "Green" , 'Table'[Avg Feb] = _2 , "Pink" , "" )If you want the Avg by Month, as per Jan and Feb in the output snapshot, then you will need to add the following measures:
Avg Feb =
VAR _1 = CALCULATE ( [Avg] , FILTER ('Table' , 'Table'[Date].[MonthNo] = 2 ) )
RETURN
_1In the above, you can see that the MonthNo 2 is February. If you want it for January, just change the 2 to a 1.
Background Avg Feb =
VAR _1 = MINX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg Feb] )
VAR _2 = MAXX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg Feb] )
RETURN
SWITCH ( TRUE() , [Avg Feb] = _1 , "Green" , 'Table'[Avg Feb] = _2 , "Pink" , "" )And the above measure, you can adjust to whatever months you want. Just change the [Avg Feb] to [Avg Jan], etc.
The other item to note is that the SWITCH TRUE gives you Green, Pink, and blank. If you want other colours, just write them in. For example, "SWITCH ( TRUE() , [Avg Feb] = _1 , "Black" , 'Table'[Avg Feb] = _2 , "Purple" , "Red" )" will give you Black background for the Avg Feb minimum, Purple for the maximum average, and red for everything else.
Hope this helps mate!
Theo 🙂
That's the problem - how to achieve correct MIN and MAX values from this measure:
AVG_2022-01_Total_amount = CALCULATE(AVERAGE(DB[Month_total_amount]),DATESBETWEEN(DB[Date],"2022-01-01","2022-01-31"))
when this measure is putted in to a table and average value is calculated for a dynamic count of sales branches, f.e.:
| Sales branch | That measure (avg) | highest value of that measure (1) |
| 1 | 54 | 0 |
| 2 | 34 | 0 |
| 3 | 65 | 1 |
Hi Daniel_Fdrvc
Below is the output you'll get by applying the steps in the PBIX attached:
You need to create the following two measures:
Avg =
VAR _1 = CALCULATE ( AVERAGE ('Table'[Amount] ) , DATESMTD ('Table'[Date] ) )
RETURN
_1
Background Avg =
VAR _1 = MINX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg] )
VAR _2 = MAXX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg] )
RETURN
SWITCH ( TRUE() , [Avg] = _1 , "Green" , 'Table'[Avg Feb] = _2 , "Pink" , "" )
If you want the Avg by Month, as per Jan and Feb in the output snapshot, then you will need to add the following measures:
Avg Feb =
VAR _1 = CALCULATE ( [Avg] , FILTER ('Table' , 'Table'[Date].[MonthNo] = 2 ) )
RETURN
_1
In the above, you can see that the MonthNo 2 is February. If you want it for January, just change the 2 to a 1.
Background Avg Feb =
VAR _1 = MINX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg Feb] )
VAR _2 = MAXX ( SUMMARIZE ( ALL ('Table' ) ,'Table'[Sales Branch] , "Avg" , AVERAGE ('Table'[Amount] ) ) , [Avg Feb] )
RETURN
SWITCH ( TRUE() , [Avg Feb] = _1 , "Green" , 'Table'[Avg Feb] = _2 , "Pink" , "" )
And the above measure, you can adjust to whatever months you want. Just change the [Avg Feb] to [Avg Jan], etc.
The other item to note is that the SWITCH TRUE gives you Green, Pink, and blank. If you want other colours, just write them in. For example, "SWITCH ( TRUE() , [Avg Feb] = _1 , "Black" , 'Table'[Avg Feb] = _2 , "Purple" , "Red" )" will give you Black background for the Avg Feb minimum, Purple for the maximum average, and red for everything else.
Hope this helps mate!
Theo 🙂