Forum Discussion
Solved
Conditional formatting matrix table where column/row values are different
I am trying to query multiple servers and return the ipconfig settings of each of our sp_configure settings. Currently, I have a different connection/query for each server in PBI and I do an "append...
djanszentql it is really great question and here is the solution, I broken down the measures in small pieces to easily understand the solution, ofcourse all this can be done in one measure as well, there are 5 measure and final KPI Color measure return the color which will be used to highlight the row
Sum of Amount = SUM ( Amount[Amount] ) Sum of Servers = CALCULATE ( [Sum of Amount], ALLSELECTED( Amount[Server] ) ) # of servers = COUNTROWS ( CALCULATETABLE( VALUES ( Amount[Server] ), ALL ( Amount ) ) ) Avg by Server = DIVIDE ( [Sum of Servers], [# of servers] ) KPI Color = IF ( [Sum of Amount] <> [Avg by Server], "Red" )On value section, choose drop down menu next to Amount to do the conditioal formatting to use KPI Color measure
djanszentql here you go, single measure except # of Server count is kept seperate since that will be used in all the measures
KPI Color Single Measure =
VAR __sumofAmount = SUM ( Amount[Amount] )
VAR __sumofServers =
CALCULATE (
SUM( Amount[Amount] ),
ALLSELECTED( Amount[Server] )
)
VAR __avgbyServer =
DIVIDE (
__sumofServers,
[# of servers]
)
RETURN
IF ( __sumofAmount <> __avgbyServer, "Red" )
Sorry, but I am still confused what Amount[Amount] is suppose to be? As in, what column are you referring to when you specify [Amount]?