cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bastien_l
Frequent Visitor

Matrix visual, sort second level column header differently depending on first column header

Hi,

 

Apologies if this has already been covered, I couldn't find an answer.
I have two products. I have monthly data for both.

I would like to show a matrix with fiscal year as rows, products as first level of column headers, months as second level of column headers (as shown below).

Problem is fiscal year is different for product 1 and product 2. I tried to put a "month_order" column where for product 1 : Oct = 0, Nov = 1 etc.. and for product 2, Sep = 0, Oct = 1, Nov = 2, etc...

When ordering by month_order, an error is thrown because Oct has two values and it doesn't like it, even if there are products in column header top level.

 

bastien_l_0-1610356222098.png

 

I could have more than 2 products in the future.

 

Thanks a lot for your help !

 

1 ACCEPTED SOLUTION

Hi @bastien_l ,

 

You are correct, my work around is not getting the values on the sort by column since has you refer it does not allows you to sort it out what I was thinking was to add a new step on tyhe hierarchy with the ID so you would get something similar to this:

 

MFelix_0-1610372343508.png

Now you can add a blank square or a line to hide that row:

MFelix_1-1610372613809.png

If you don't want to add that additional row the only way to have it done is to place also the number of the product on the month itself something similar to 1.Oct 1. Nov 1. Dec - 2.Sep 2.Oct 2.Nov and then sort this column by the ID.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User III
Super User III

Hi @bastien_l ,

 

How do you know the correct order of the dates within products? Asking this because if you only have two products would would only need to use a disconnected dimension table where you would place the order of the products months something similar to this.

 

Product Month SortID
Product1 Oct 0101
Product1 Nov 0102
... ... ...
Product1 Sep 0112
Product2 Sep 0201
Product2 Oct 0202
... ... ...
Product2 Aug 0212

 

The using a switch function you could calculate the values you need.

 

Is there any table or field where you know that the first month of the fiscal year is October for product 1 and September for Product 2?

 

Also be aware that this calculation for the switch need to take into account the year.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



bastien_l
Frequent Visitor

Thanks for your reply. Indeed I have a table where I have product / fiscal year start columns where I know that product 1 starts 9 and product 2 starts 10
I had tried the solution you suggests above but problem is, when I want to sort "Month" by "SortID", Oct for example will have "0101" and "0202" and power BI throws me an error :

"We can't sort the 'Month' column by 'SortID'. There can't be more than one value in 'SortID' for the same value in 'Month'. Please choose a different column for sorting or update the data in 'SortID'"

Hi @bastien_l ,

 

You are correct, my work around is not getting the values on the sort by column since has you refer it does not allows you to sort it out what I was thinking was to add a new step on tyhe hierarchy with the ID so you would get something similar to this:

 

MFelix_0-1610372343508.png

Now you can add a blank square or a line to hide that row:

MFelix_1-1610372613809.png

If you don't want to add that additional row the only way to have it done is to place also the number of the product on the month itself something similar to 1.Oct 1. Nov 1. Dec - 2.Sep 2.Oct 2.Nov and then sort this column by the ID.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.