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

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's avatar
    lbendlin
    Super User
    3 years ago

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