Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
amsrivastavaa
Helper III
Helper III

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

amsrivastavaa_3-1670594344575.png

 

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

 

amsrivastavaa_1-1670593341271.png

 

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

amsrivastavaa_2-1670593790387.png

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

 

 

 

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

View solution in original post

10 REPLIES 10
v-binbinyu-msft
Community Support
Community Support

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 )

vbinbinyumsft_0-1671082045962.png

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.

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

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.

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

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

 

lbendlin_1-1671750718417.png

 

 

Issue 2 can be solved the same way.

See attached.

 

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 

 

amsrivastavaa_0-1671775467090.png

 

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

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

Hi @lbendlin ,

 

Thanks for the solution!!!

 

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

 

Regards

A

lbendlin
Super User
Super User

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

Hi @lbendlin ,

This is my source data 

amsrivastavaa_0-1670820375251.png

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

  amsrivastavaa_1-1670820468963.png

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.

amsrivastavaa_2-1670820595839.png

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.