Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Helper 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...
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")
amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago

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's avatar
lbendlin
Super User
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")
  • amsrivastavaa's avatar
    amsrivastavaa
    Helper III
    3 years ago

    Hi lbendlin ,

     

    Thanks for the solution!!!

     

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

     

    Regards

    A