Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PowerBear
Regular Visitor

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

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

PowerBear_0-1695490758554.png


figure

PowerBear_1-1695490773784.png

 

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

PowerBear_2-1695490802390.png


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

 

Thanks

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

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
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@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.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.