How to create filters in Power BI from Excel Pivot table data?

[PowerBear]

Hi Friends,

I have some data and figures from Excel pivot tables. They’ve already filtered in Excel pivot tables. Now, I’d like to plot them in Power BI with some slicers.

The data and figure in Excel are shown below:
data

figure

The demo figure in Power BI is something like this. It includes 2 filters, Year and class(A, B, C)

My question is how to create these filters in Power BI?

Thanks

[Syndicate_Admin]

I would like to express my sincere thanks for all the valuable information you share in this forum. It has been a great help in resolving my doubts and keeping me updated on topics related to [forum topic]. The community here is amazing and I really appreciate the time and effort you all put into helping each other.

Thank you!

[parry2k]

@PowerBear just follow the instructions I provided and you don't need to do anything crazy in Power BI. you can easily unpivot it in Power BI.

[parry2k]

@PowerBear first of all, as a best practice you should unpivot your data, and then everything will become super easy.

You can easily transform your data in Power Query in Power BI. 

First split the Term column into two columns Year and Category. Remove the Term column, select new columns (Year and Category) -> right click -> Unpivot other columns. This will give you two new columns, Attribute and Value, rename those as you see fit. Close and Apply.

To visualize, you can use attribute columns on x-axis, value on y-axis, Year and Category columns as slicers and everything will flow nicely.

[PowerBear]

Hi @parry2k 

Thanks for the reply. 

I think my issue now is how to unpivot thoese pivot tables, because the data table I show here is calculated from those pivot tables. If I unpivot those Excel pivot tables, I need to do some calculations in PBI using DAX, probably. 

Let me think how to solve this issue based on your advice. Thanks again!