Trying to create this visualization - Matrix or R Script?

[kanderson521]

Hello,

I am looking for advice on the best path to create the visual in the image below. I have already toyed with pivoting & unpivoting columns and get close but not exactly to the specifics of the image below. Any ideas or suggestions are greatly appreciated.

Thanks in advance,

Kai

[Anonymous]

I would imagine that a Matrix visual is going to be your best bet.  You'll be able to put columns of data into the "Rows" and "Columns" to take your unpivoted raw data and place it into that style of information display.

Matrix visual also has a function for conditional formatting to get your colouring.

[kanderson521]

Here's what my source data looks like -- where I'm struggling is how to pivot so that I am getting sums for all the categories instead of individually or just for one, seems like there should be a way to sum for all columns  then create a measure that shows the percentage of goal achieved, sort of like a KPI but in a table format.

[v-caliao-msft]

@kanderson521,

Based on your sample data, you need to unpivot your data and then add the column to Column in Matrix visual. Please refer to the link below to see the detail about Pivot and UnPivot in Power BI.
http://radacad.com/pivot-and-unpivot-with-power-bi

Regards,

Charlie Liao

[kanderson521]

Hello and thank you for that suggestion -- that is what I initally thought of trying and it gets me to the point where I can see the actuals, but I'm not able to see both goals and actuals? Of the 13 criteria (colums) 10 have goals that I need to aggregate a percentage of completion on. I tried the switch technique with the metrics table and it worked great but would not allow me to use conditional formatting on the measures

http://angryanalyticsblog.azurewebsites.net/index.php/2017/07/13/performance-scorecards-and-heatmaps...

So I came back to pivoting/unpivoting... I'm not sure what I am missing here, any help you can provide is very much appreciated.

Kai