Heat Map

[Anonymous]

I am working on a heat map and able to do the same in excel but need to push that data in power bi. We have a list of solutions and every soulution as a Privacy By Desing (PBD) and  Security By Design(SBD) score.

if there are two solution whoe PBD score is 5 and SBD score is 5 then on the top right I will get 2 for that corresponding column and row

If there are three soltuion whose PBD score is 5 and SBD score is 4 then that correspondign column and row will have value 3

Below is the source table in excel

[DataZoe · Accepted solution]

@Anonymous 

I was able to get close in Power BI with this visual:

Please see the attached PBIX.

There were a couple things to do on this one.

1. create the seperate tables for the SBD and PBD values, this allows for a context for the 0 solutions. To do this I went to Modeling ribbon > New Table and used this DAX Expression:

PBD = DISTINCT('Table'[PBD])

and same for SBD. For SBD I used PBD  (because there was no 0 in the data for SBD!) then renamed the column to SBD.

2. Create the relationships between SBD - SBD and PBD - PBD:

3. I created a measure (right-click table > New Measure) for the solutions (there has to be a 0 for the background to show!)

Solutions = DISTINCTCOUNT('Table'[Solution])+0

4. I created another measure for the Background:

Background Color =

var sv_sbd = SELECTEDVALUE('SBD'[SBD])

var sv_pbd = SELECTEDVALUE('PBD'[PBD])

var multi = sv_pbd*sv_sbd

return

SWITCH(

TRUE(),

and(sv_pbd<= 2, sv_sbd <= 2),"Red",

and(sv_pbd>=4, sv_sbd>=4),"Green",

"Yellow")

5. I created another measure to hide the 0s by using Font Color:

Font Color = if([Solutions]=0,[Background Color])

6. On the table, I did conditional formatting:

For background, use format by Field Value and choose the Background Color measure:

For font, use the format by Field Value and choose the Font Color measure. 

7. I added a shape and made the background white to cover the top left corner.

8. I added a shape and made the column title box.

9. I added a shape and rotated it 270 degrees to make the row title box.

10. In View ribbon > Selection I then gave the shapes meaningful names and grouped all the visuals together so I could move them together easily. 

Hope this helps!

[Anonymous · Accepted solution]

Thank you @DataZoe.

[DataZoe]

You are welcome @Anonymous, did this answer your question? 

[Anonymous]

Yes DataZoe, it solved my problem

[DataZoe · Accepted solution]

@Anonymous 

I was able to get close in Power BI with this visual:

Please see the attached PBIX.

There were a couple things to do on this one.

1. create the seperate tables for the SBD and PBD values, this allows for a context for the 0 solutions. To do this I went to Modeling ribbon > New Table and used this DAX Expression:

PBD = DISTINCT('Table'[PBD])

and same for SBD. For SBD I used PBD  (because there was no 0 in the data for SBD!) then renamed the column to SBD.

2. Create the relationships between SBD - SBD and PBD - PBD:

3. I created a measure (right-click table > New Measure) for the solutions (there has to be a 0 for the background to show!)

Solutions = DISTINCTCOUNT('Table'[Solution])+0

4. I created another measure for the Background:

Background Color =

var sv_sbd = SELECTEDVALUE('SBD'[SBD])

var sv_pbd = SELECTEDVALUE('PBD'[PBD])

var multi = sv_pbd*sv_sbd

return

SWITCH(

TRUE(),

and(sv_pbd<= 2, sv_sbd <= 2),"Red",

and(sv_pbd>=4, sv_sbd>=4),"Green",

"Yellow")

5. I created another measure to hide the 0s by using Font Color:

Font Color = if([Solutions]=0,[Background Color])

6. On the table, I did conditional formatting:

For background, use format by Field Value and choose the Background Color measure:

For font, use the format by Field Value and choose the Font Color measure. 

7. I added a shape and made the background white to cover the top left corner.

8. I added a shape and made the column title box.

9. I added a shape and rotated it 270 degrees to make the row title box.

10. In View ribbon > Selection I then gave the shapes meaningful names and grouped all the visuals together so I could move them together easily. 

Hope this helps!