Show both Quarter and Full Year on same axis/as columns

[kevpuk]

Hi

I have been struggling with a problem and am hoping that the collective will be able to assist 🙂

So, I have various data feeds (all defined scheduled reports in Excel) that arrive in my .pbix, and the common date field I have used to model them together is calendar date e.g. 01 January 2015. I have created a 10yr date table (we will all be in flying cars and not using Power BI after 10yrs?) which allows the various source tables to map their calendar date field against, to give me Qtr, Year and so on - this works just fine.

Where I am struggling is with the need to display data in various charts and/or matrix visuals with the following layout:

link to hastily knocked up Excel chart to demonstrate

Essentially, I am struggling to get an approach to show, for example, one column being 2018, the next being 2Q 18, the next 3Q 18 and the next 4Q 18........so, to combine both Qtrs and FYs as columns/one axis.

Grouping is not suitable, as I need to show - for example - both 2Q18, 3Q18 and 4Q18 as well as 'full' 2018.

Hope there is some light at the end of this tunnel 🙂

[v-shex-msft]

HI @kevpuk ,

You can add a calculated column to display more details category labels, then add a table to setup custom sort order of these new labels.

Custom Sorting in Power BI

Regards,

Xiaoxin Sheng

[kevpuk]

Thanks for the response.

The problem is that I need to be able to aggregate items under both the Quarter and respective Year in the chart/matrix; all items that occurred in 3Q 18, for example, will also need to show in 2018. I cannot find an approach that allows this.

If I use a calculated column, to give an assigned value, then said value would need to be either 3Q 18 or 2018 for a given row, whereas what I am hoping to achieve is that I can show both 3Q 18 value and 2018 value in same visual.

The other approach I considered was duplicating all rows, perhaps by copying and merging a table of all 2018 quarter data, and then halving values displayed....but not sure that is elegant, either.

Is it possible to create a measure for the 'time period'? This might allow the overlap I am facing.....?

[v-shex-msft]

HI @kevpuk ,

So you means you wanted to break current category to allow it shows more data label in previous category?

If this is a case, you can consider to create a table with original category and new grouped records, build relationship from original table to new table based on category fields. After these steps, you can use new table fields to replace original table fields to let visitation display more items.

Regards,

Xiaoxin Sheng