Unpivot - grouping measure columns and create a fact table

[Learnbyexpert]

Dear Power Experts,

We have a excel source with below formatted columns(below columns are sample,there are few more columns), we would to create a fact table or relavant unpivot.

Program|Centre|Activity Date|Singing|Dancing|Poietry|Male|Female|Others|Feedback|Rating

East|Sonic|01-13-2018|0|2|5|2|4|0|Good|4

East|Enos|01-13-2018|4|2|2|2|4|3|Good|5

East|Park|01-13-2018|0|2|0|2|4|0|Good|4

West|Sonic|01-13-2018|0|2|5|2|4|0|Good|4

West|Enos|01-20-2018|402|2|4|4|3|Good|5

East|Park|01-25-2018|0|2|0|1|4|2|Good|4

East|Park|01-25-2018|0|2|0|2|2|0|Good|4

We need to group columns{4,5,6} as Cultural and columns{7,8,9} as Participants. This can be achieved by suming each set of columns. But the requirement here is,

We need to show each group columns and value in bar chart.

Bar chart one:

Singing 

Dancing

Poietry

Bar chart Two:

Male

Female

Others

And when we select Dancing in bar chart one then dynamically Bar charts value should change vice-versa.

Same, whent we select Program, Centre or date each should filter dynamically.

How do we achieve in date model?

Really appreciated any help with this topic.

[jbwtp]

Hi @Learnbyexpert,

I think the data which you have may not beable to provide you the functionality which you require (as I read it).

The bar chart itself is not a problem: you can either get away with using the source table columns (as ledgend) or split it to 2 (or separate tables): by culture and by participant and then unpivot data in each of them.

However, this does not solve the rest of your task: filtering participant by cultural (chart two on chart one selection).

I do not see a way how the data would allow it. For example, based on the data provided. What would be your expectation for the values in the Chart Two (by participant) if Dancing is selected on the Chart One (by cultural).

Thanks,

John

[Learnbyexpert]

Thanks John for your valuable time.