Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Icon for Helper III rankHelper III
3 years ago
Solved

Power BI Matrix with Color Coding

Hi Guys!!!

 

I have data as below : i.e. Yearly data of Project that includes Type, license and Input detail. 

 

For illustration purpose, I have shown TYPE=CREDIT only, but there can be multiple TYPE as well, such as DEBIT, NET PROFIT etc

 

I have to create Power BI report with below details

 

Filters :

Filter in Report : 

1. Year 

2. Type

3. License.

 

Matrix  Visual: 

Matrix shows data of Type credit and its shows all data for all the year with shown coloring of the values 

 

Logic For color : 

1. If Bigger Year Input value > Previous selected year == GREEN

2. If Bigger Year Input value = Previous selected year == BLACK

2. If Bigger Year Input value < Previous selected year == RED

 

 

Till, here I am good, and able to achieve the feet.

 

Where I GOT stuck is, I mean, need you guys help is detailed below 

 

Problem : Requirement is to provide color coding to the value based on last year available in the matrix visual

 

Detail is as below 

 

Lets say, user selected filter as below

 

Year = ALL

License = A

Type =ALL

 

As soon as User selects License=A in filter, only records for Year 2018, 2020 and 2022 will qualify and matrix will be shown as below

P.S> I am not able to achieve this color coding based on available year data in Matrix visual.

 

I.e. I need to provide COLOR to the values  based on last year available values.

I.e. In this case, 2020 value need to be compared with 2018, 2022 to be compared with 2020.

 

2018 VALUE [40] - need to be BLACK, as there is not previous value

2020 VALUE [50] - need to be GREEN, as previous available value is 2018 and it is 40, i.e. less than 50, so GREEN

2022 VALUE [40] - need to be RED, as previous available value is 2020 and it is 50, i.e. more than 40, so RED

 

VALUES Need to color code based on LAST YEAR available value at Matrix visual.

 

 

Note : we can have multiple slicer along with LICENSE, and need this logic to be applied for all filters available in Report

Please suggest !!!

 

Thanks

A

 

 

 

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    Here is a more pedestrian version. I am sure it can be improved - please check the performance.

     

    BG = 
var a = CALCULATETABLE(SUMMARIZE('Year Table','Year Table'[dYear],"v",[NP_Max_Common_Projects]),ALLSELECTED('Year Table'[dYear]))
var cy = max('Year Table'[dYear])
var py = maxx(filter(a,[dYear]<cy),[dYear])
var pv = sumx(filter(a,[dYear]=py),[v])
var c = [NP_Max_Common_Projects]
return SWITCH(TRUE(),isblank(py),"black",c>pv,"green",c<pv,"red","black")

10 Replies

    • amsrivastavaa's avatar
      amsrivastavaa
      Icon for Helper III rankHelper III
      3 years ago

      Hi lbendlin ,

      This is my source data 

      I need to create Matrix report with conditional formating on INPUT column with SLICERS on report  as shown below 

      Slicers : 1. Year    2. Type  3. License.

      Report Layout

        

      Color Coding Logic : 

      1. If  Year Input value > Previous selected year == GREEN

      2. If  Year Input value = Previous selected year == BLACK

      3. If  Year Input value < Previous selected year == RED

      Till here, I am able to achieve this.

       

      Requirement 

      I am not getting correct color coding once user selects below combination of slicers

      Year = ALL  ; License = A;  Type =ALL

      I.e, once Slicers are provided, data is not avilable for all the years, here we are not having data for 2017,2019 and 2021.

      I want, color coding to be based on the YEAR which is available on the matrix, as shown below

      i.e. in below example, 2022 will look inot 2020 data not 2021, similarly 2020 will look for 2018 not 2019 in this case.

      I need, color coding to be implemented based on the previous year available in the matrix.

      lbendlin ; Please let me know in case you still need clarification, i will provide you dummy PBIX report.

       

      Thanks

      A

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi amsrivastavaa ,

    Please create a measure with below dax formula:

    Measure =
VAR _a =
    MAX ( 'Table'[Year] )
VAR _b =
    CALCULATE (
        MAXX ( FILTER ( 'Table', [Year] < _a ), [Year] ),
        REMOVEFILTERS ( 'Table'[Year] )
    )
VAR _c =
    CALCULATE (
        SUMX ( FILTER ( 'Table', [Year] = _b ), [Input] ),
        REMOVEFILTERS ( 'Table'[Year] )
    )
VAR _d =
    SUM ( 'Table'[Input] )
VAR _e =
    SWITCH ( TRUE (), _c < _d, "Green", ( _c = _d ), "Black", _c > _d, "Red" )
RETURN
    IF ( ISBLANK ( _c ), "Black", _e )

    Please refer attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago

        Your measure 

        all_project_Comon Projects with Net Profit
         
        is mixing numerical values and text values.  I would recommend not to use the FORMAT function but the standard formatting options.