Filtering help

[mork]

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?

[chrisu · Accepted solution]

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.  

[mork]

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