Forum Discussion
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" in Power Query to put everything into a single table.
I want to highlight entire rows where the values in the columns do not match one another.
Again, my data is all in a single table where I am pivoting on the "Server" column.
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
12 Replies
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
I would have never figured this out on my own so thank you very much.
You mentioned doing this in one variable.. how could I do that? I only ask because at the moment this solution only highlights the row with label 'Sum of config_value'. I would have to duplicate all of those measures for run_value, minimum, and maximum.. which I would obviously prefer not to do only for the sake of cleanliness in my PBI report. Any further help is much appreciated.
djanszentql based on you dataset it should work for every row. May be I missed something. You don't need to calculate it for every row.
