Problem with rendering Columns of a Matrix table from another Table

[Anonymous]

Hi All,

I am creating a Pivot like table on the Sales data which looks as follows. I want to Pivot by Category along rows and Month along colunms.

Source data

Expected pivot (fromExcel)

My approach - Create another table with the list of Months

Outocome in Power BI

I have used the "Show items with no data" . 

Challenge

Power BI has displayed columns from Jan to Dec. I want Power BI to restrict to values in the list of months

Any suggestions?

Thanks,

Sau

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

I am sorry for the misunderstanding. I recreated data to reproduce your scenario.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is a many-to-one relationship between two tables.

Then you may use the date column from Calendar table to create a matrix visual. Do make sure you check the 'show items with no data' and use visual level filter to display the result. Here is the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

You may create a matrix visual as follows.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Use the Filter pane?

[amitchandak]

Try and do this option month , and see if it can help