Pivot Chart Partial Grouping

[kelvin-lkh]

Dear fellow PBI pro,

Below are the chart I plot from PBI and the X-axis are in quater and down to period. I wish to show Q1, Q2, Q3, P10, P11, P12 in my chart instead of the below P01-P12. How do I make the charge show by quarter for P01-P09 and then by month period for P10 to P12?

 

[Chihiro]

You can just add Label column.
In query editor add custom column...

= if [Quarter] <> "Q4" then [Quarter] else [Period]

Note that you will need to add sort column (i.e. Position) for this column. Or it will not sort from Q1-Q3, P10-P12 order.

Alternately you can create visual via R or Python and perform dataframe transformation in script.

 

[p45cal]

You can also group Period manually into Period2:


then only plot Period2:

 

[Chihiro]

It's PowerBI, it enforces rules in Data Model side and not using pivot layer. Pivot/Matrix visual and Chart in PBI isn't directly connected via fields, only through data relationships.

 

[kelvin-lkh]

Hi Chihiro,

I have created order for period and link it to the original table. I also pull in the 'Order' column into the chart. However, the sorting function only show the first hierarchy in the Axia as below pic. How do I do the sorting option as per your suggestion? I wish to upload my PBI file but this forum does not allow to attached the file in PBI format.

 

[Chihiro]

You need to select the column and the go to model ribbon and define which column is used to sort "New Period".

 

[kelvin-lkh]

it works. Thanks a lot Chihiro Pro!!