cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mork
Helper V
Helper V

Filtering help

Hello,

 

I want to construct  table on excel that will show the sales of each of 4 departments in a range of twelve months. Then I want to create a graph of this table in Power BI that I can filter per department with a slicer.

 

So basically for my graph I'll need a column called Months for the x-axis and as values the sales of each department for each month. I came up with the table bellow in excel.

 

 

Months     Dept.1      Dept.2    Dept.3    Dept.4

January        25            25           24         23 

February       34           34           33         32

etc.

 

I cant create the line graph fine by using as values the columns Dept.1 - 4.

 

But how can I now use a slicer to filter that graph per department? That way I will use just one graph to show all the departments together or just one at a time if I filter.

 

There is no Departments column to use on a slicer as in my table each department is a column header.

 

If I rotate my table 90 degrees I will have a departments column but I won't have a month column to use as an axis.

 

Is there something I'm missing? This must be something easy to solve, am I right? Should I construct my table in te excel file in another way or is there something that I should inside Power BI?

1 ACCEPTED SOLUTION
chrisu
Responsive Resident
Responsive Resident

What you'll want to do is unpivot the Dept. columns in the query editor.  You note that rotating your table 90 degrees (unpivoting) doesn't work, but from what I see it should.  Your resulting data will look something like:

 

Month    Dept  Sales

Jan        1        25

Jan        2        25

Feb       1        34

Feb        2       34

 

etc.

 

Then when you place month and dept as axes, your sum of value will automatically filter by month and dept.  You can also add a Month and a Dept slicer since they are their own columns.  

View solution in original post

2 REPLIES 2
chrisu
Responsive Resident
Responsive Resident

What you'll want to do is unpivot the Dept. columns in the query editor.  You note that rotating your table 90 degrees (unpivoting) doesn't work, but from what I see it should.  Your resulting data will look something like:

 

Month    Dept  Sales

Jan        1        25

Jan        2        25

Feb       1        34

Feb        2       34

 

etc.

 

Then when you place month and dept as axes, your sum of value will automatically filter by month and dept.  You can also add a Month and a Dept slicer since they are their own columns.  

View solution in original post

@chrisu

 

Oh my god I have no idea why I didn't think of that. And in my other excels I'm using tables formated that way. hahaha. thanks a lot!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!