Power BI Matrix with Color Coding

[amsrivastavaa]

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

[v-binbinyu-msft]

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.

[amsrivastavaa]

Hi @v-binbinyu-msft ,

I tried to incorporate the same in my report but not able to succeed.

I am here by sharing my PBIX (Tab - 15 DEC)

https://1drv.ms/u/s!Ahtm7otFIxr8fhwpEVHDNUgdwnM?e=PHeAHA

I have created Measure wwich you have suggested but its not working.

Please suggest!!

Thanks

A

[lbendlin]

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.

[amsrivastavaa]

Hi @lbendlin ,

I have sorted up my report and same has been shared at below URL 

URL : https://1drv.ms/u/s!Ahtm7otFIxr8f5rq6iGfIo5IlEo?e=azauXq

Now, I am getting data correctly (not getting blank values) however still not getting COLOR code, also please suggest how to perform color code for Growth Rate (Issue is detailed in the Report)

Please suggest!!

Thanks

A

[lbendlin]

Issue 1:

BG = 
var cy = max('Year Table'[dYear])
var my = CALCULATE(min('Year Table'[dYear]),ALLSELECTED('Year Table'[dYear]))
var py = CALCULATE(LASTNONBLANK(fTable[Year],[NP_Max_Common_Projects]),REMOVEFILTERS('Year Table'[dYear]),fTable[Year]<cy)
var pv = CALCULATE([NP_Max_Common_Projects],'Year Table'[dYear]=py)
var c = [NP_Max_Common_Projects]
return SWITCH(TRUE(),cy=my,"black",c>pv,"green",c<pv,"red","black")

Issue 2 can be solved the same way.

See attached.

[amsrivastavaa]

Hi @lbendlin ,

It works well for this scenario however it fails for another scenaio.

Scenario : 

Let say user has selected Channel=NO and he/she is able to see data for 2017.2018,2022 and 2023, till that point, color coding works well as you have shown above.

But, now, let say, user unselected Year 2022 from Year slicer, in that case its not working, it shows data as below 

Ideally, Value (98) for Year=2023 must be GREEN.

I have attached the report as well with this scenario.

URL - 

https://1drv.ms/u/s!AhouqzePY6QjgjatloPTV-4WF7oU?e=LcmYZc

Please suggest!!

Thanks

A

[lbendlin · Accepted solution]

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")

[amsrivastavaa]

Hi @lbendlin ,

Thanks for the solution!!!

For me, this is solution but i am not getting option to mark as Solution.

Regards

A

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[amsrivastavaa]

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