Not sure if I can achieve this with PowerPivot any insights welcome

[Mr Excelinator]

Hello all.

I'll explain what I'm trying to do, I'm unsure if I can achieve this with Power Pivot, I'm fairly new to PPvt & gotten to grip with the basics.

So my source data looks like the below, which is many 000s of rows, formatted as a table & is in my data model.[TABLE="class: cms_table, width: 334"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data Source[/TD]
[TD]Person ID[/TD]
[TD]Value A[/TD]
[TD]Value B[/TD]
[TD]Value C[/TD]
[/TR]
[TR]
[TD]FY16 10[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]FY17 11[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]175[/TD]
[/TR]
[TR]
[TD]FY17 11[/TD]
[TD="align: right"]654321[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]200[/TD]
[/TR]
</tbody>[/TABLE]


What I want to do with it is generate a report something akin to the below:

[TABLE="class: cms_table, width: 383"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data Source:[/TD]
[TD]FY17 11[/TD]
[TD]FY16 10[/TD]
[TD]FY17 11[/TD]
[TD]FY17 11[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Person ID[/TD]
[TD]Value A[/TD]
[TD]Value A[/TD]
[TD]Value B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]654321[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[/TR]
</tbody>[/TABLE]


So I am creating a report where the main population will be selected from data source eg FY1711 and I can compare the Value data sets, by their data source, like the above, so A vs A, A vs B etc cut by the Data Source.

If anyone has any advice on how to achieve this (power pivot or otherwise) be greatly appreciated.

Cheers.
Stu.

 

[Matt Allington]

I suggest you unpivot the last 3 columns into an attribute and value column.

Source, person, attribute, value
Fy17 11,123456,Value A, 50
etc

Use Power Query for that. This will give you a table of Data that can be used in a pivot table to build anything you need.

 

[Mr Excelinator]

Thank you Matt I see the real value in unpivot & creating attribute & value column. As sais I'm fairly new into this but really liking what I See.

The bit I am struggling with after that, is how to in effect create a 'selective' report, using a pivot table to get me what I need, which cherry picks the attribute variants I want to compare,

In my example you see I wanted to create a 'report' comparing selected data source/value combinations from my dataset, in the example I took two data sources from Value A, and one data source from value B (extrapolate that to 1000's of data source variants & 100s of value sets) - hope you can see my issue,

What I have been able to create is a pivot table with all of the data in it, but struggling to get the above accomplished as a neat report.

If you have any ideas most welcome,
Thanks in advance.
Stu

 

[Matt Allington]

I don’t really understand the issue a simple I don’t know what is not shown. All I can see is a table with 3 rows and 3 columns of data displayed in a different way. I couldn’t really comment without seeing a scale example with use cases.

 

[SpillerBD]

I think i see what your going for. For the "Person ID's that were member of the "FY17 11" data set, what were their values in select data sources and "Values A" B etc.
You need to a Table to indicate member by DataSource for each person.
I am seeing two different tables generated by PowerQuery and likely the need for PowerPivot to combine the desired data for your columns correctly.