Forum Discussion

djanszentql's avatar
djanszentql
Helper I
6 years ago
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...
  • parry2k's avatar
    6 years ago

    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