[Beginner Question] Data structuring tall to wide for table visual

[CB13]

Hello,

Beginner here so apologies if this is simple, but after quite a few failed attempts, I could use help figuring out how to shape my data / build my visual.

The report needs to be a grid with metrics across the top, and people as rows:

	Metric A	Metric B	Metric C
Person 1	98%	1.5	75%
Person 2	97%	1.1	44%
Person 3	100%	2.4	50%

My data is stacked, and I currently use a matrix visual to flatten it, as well as use the Indicator field to apply conditional formatting (the rules for the conditional formatting vary widely by metric):

Person	Metric	Result	Indicator
Person 1	Metric A	98%	1
Person 1	Metric B	1.5	0
Person 1	Metric C	75%	1
Person 2	Metric A	97%	1
Person 2	Metric B	1.1	0
Person 2	Metric C	44%	0
Person 3	Metric A	100%	1
Person 3	Metric B	2.4	1
Person 3	Metric C	50%	0

All results are pre-calculated prior to load, so I do not need to perform any aggregations. The matrix solution achieves the look I need, and from what I have read should be more performant than a flat table, but has 2 issues the user has said are now must-haves:

1. When they export the data it must export in the same structure/format as the displayed grid (not stacked like the source data). 

2. They need to be able to sort by the metric results, for example, sort by Metric C to see all the top/bottom results on that metric.

The user still wants to be able to select / de-select metrics to have them show / not show in the grid (easily achieved using the stacked data in a matrix, not so much using a table).

This is leading me towards flattening my data and using a table visual instead of a matrix visual. I have tried:

1. Using Pivot to flatten the stacked data in Power BI. Since I still need my stacked data for other report pages I was hoping this would be more efficient than re-loading the same data re-structured? I wrote 2 new queries referencing the first, 1 to flatten the metric results and 1 to flatten to indicator, and joined them back together in my data model.

2. Flattening the data into 2 tables (result, corresponding indicator) prior to loading to Power BI and joining in my data model.

Since I have > 60 metrics, applying the conditional formatting is time consuming, so I want to try to ensure I'm on the right track before fully investing.

A few questions:

> Is there a better solution using stacked data directly that can achieve my goal? My stacked table is > 13mil rows currently, so I want to be careful not to duplicate/triplicate it if not absolutely necessary. Does it make any difference for performance whether I pivot the 2 flat tables in Power BI or prior to Power BI (the latter is far more familiar for me since I can code it via SAS)?

> Is there a way to allow users to adjust which metrics are included? I've seen solutions creating a reference table for line charts, but I haven't figured out how to do this in my tables yet.

Thanks in advance for any help / suggestions!

[v-danhe-msft]

Hi @CB13 ,

What is your desired result? Could you please post a desired result if possible? And based on my test, it seemed you could just use Matrix to achieve this visual?

Regards,

Daniel He

[CB13]

Hi@v-danhe-msft - thanks for the reply. You are correct, I can achieve the desired visual using a matrix visual as you have shown. Unfortunately, my clients have now made the requirement such that when they *export* that data (to excel) it also looks the way it does in the matrix visual (with the metrics across the top). 

Since the data is stacked, not flat, the export does not "match" the layout of the visual and this is not sufficient for their needs.  The first table in my original question is the desired result - both in the visual and upon export, but so far it seems like in order to get the export to appear that way, I have to structure the data that way. I am concerned with performance / effort given the volumne of metrics that would be flattened, and wondering where it makes the most sense to do this restructuring.

Thanks!