Heatmap on Matrix Visual as per specified format

[AjayBI55]

Hello,

I wanted to create a heatmap on Matrix Visual depends on below specified format. For that we need create Probability and Imapct columns as per below given rules. 

Expected Matrix Visual:

Rules:

The Risk table as below and i have calculated cost /sales price and riskscore using risk table.

Risk Table:

 

Calculated columns:

I have created a BG colour column using DAX. But it doesn't give result as per expected visual. 

My result matrix Chart

Fields

Imapct Calculated column DAX

Impact = var cost = SUMX(RELATEDTABLE('sales price'),risks[cost]/'sales price'[sales price]) *100

return

SWITCH(TRUE(),

cost>=1,"Very High Impact",

cost>=0.5,"High Impact",

cost>=0.25,"Medium Impact",

cost>=0.1,"Low Impact","Very Low Impact")

Probabilty Calculated column DAX

Probabilty = SWITCH(TRUE(),

risks[likehood] >=40,"Highly Probable",

risks[likehood] >=30,"Probable",

risks[likehood] >=20,"Possible",

risks[likehood] >=10,"Unlikely","Rare")

BG Colour Calculated column DAX

BG_Colour_Column = var cost = SUMX(RELATEDTABLE('sales price'),risks[cost]/'sales price'[sales price]) *100

RETURN

SWITCH(TRUE(),

COST<0.1 && risks[likehood] >=40, "#ffff00",

COST<0.1 && risks[likehood] >=30, "#ffff00",

COST<0.1 && risks[likehood] >=20, "#008000",

COST<0.1 && risks[likehood] >=10, "#008000",

COST<0.1 && risks[likehood] <10, "#008000",

COST>=0.1 && risks[likehood] >=40, "#FFA500",

COST>=0.1 && risks[likehood] >=30, "#ffff00",

COST>=0.1 && risks[likehood] >=20, "#ffff00",

COST>=0.1 && risks[likehood] >=10, "#ffff00",

COST>=0.1 && risks[likehood] <10, "#008000",

COST>=0.25 && risks[likehood] >=40, "#FFA500",

COST>=0.25 && risks[likehood] >=30, "#FFA500",

COST>=0.25 && risks[likehood] >=20, "#ffff00",

COST>=0.25 && risks[likehood] >=10, "#ffff00",

COST>=0.25 && risks[likehood] <10, "#008000",

COST>=0.5 && risks[likehood] >=40, "#FF0000",

COST>=0.5 && risks[likehood] >=30, "#FFA500",

COST>=0.5 && risks[likehood] >=20, "#FFA500",

COST>=0.5 && risks[likehood] >=10, "#ffff00",

COST>=0.5 && risks[likehood] <10, "#ffff00",

COST>=1 && risks[likehood] >=40, "#FF0000",

COST>=1 && risks[likehood] >=30, "#FF0000",

COST>=1 && risks[likehood] >=20, "#FFA500",

COST>=1 && risks[likehood] >=10, "#FFA500",

COST>=1 && risks[likehood] <10, "#ffff00")

Could you please help me...

Best

Ajay

[lbendlin]

You are nearly there.  (you should do the color coding in Power Query, not in DAX)

Remember that to report on things that are not there you need to use disconnected tables and/or crossjoins.  Otherwise you won't get background colors for BLANK() cells.