Sort by Column Problem

[Mat42]

I have a table of data that looks similar to this:

Item No,	Actual Quarter	Quarter Lag	Reporting Quarter	Value
1	Q1	None	Q1	100
1	Q2	None	Q2	200
1	Q3	None	Q3	300
1	Q4	None	Q4	400
2	Q2	None	Q2	111
3	Q1	1	Q4	555
3	Q2	1	Q1	666
3	Q3	1	Q2	777
3	Q4	1	Q3	888
4	Q2	None	Q2	99
4	Q4	None	Q4	77

The data concerns counts of items in a given financial quarter, with some being counted quarterly (items 1 and 3), some being counted annnualy (item 2) and some being counted biannually (item 4). However, Item 3, despite being counted quarterly, is actually in a lag, which means that when we count it in quarter 1, the figure actually pertains to quarter 4.

The graph I've put together is a simple column chart using the Reporting Quarter as the axis and Quarter Values as values. The graph is adjusted with a slicer populated by Item No.

My problem is that when item 1,2,4 are chosen from the slicer the Reporting Quarters are displayed in the correct order (ascending numerically). Item 3 also displays in the same order...which is incorrect for that item as it needs to start at 4 (the order being 4,1,2,3). Obviously, if I adjust the order to accommodate that item, every other item will be incorrect.

I've tried using the 'Order by Column' option and ordering using the 'Actual Quarter' column but PBI doesn't like it, throwing up the 'Sort by another column' error message. I've tried adding in an index column and using that to order by, but it doesn't like that either.

Can anyone give me ideas on how to get the graph to display data consistently in the order that it should appear?

[v-yingjl · Accepted solution]

Hi @Mat42 ,

Based on your descritption, your expected output may like this:

 Click the (...) options to sort by asc based on the value field:

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.