Forum Discussion

AjayBI55's avatar
AjayBI55
Frequent Visitor
3 years ago

Heatmap on Matrix Visual as per specified format

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

 

Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    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.