topic Re: Chart showing totals by quarters and months of current quarter in DAX Commands and Tips

Chart showing totals by quarters and months of current quarter

pmazz63 — Tue, 01 Sep 2020 14:49:39 GMT

Hi all,

I am new to Power BI and trying to figure out how to achieve this; I spent several hours on failed attempts and also searching posts with similar challenges but could not find anything applicable to my requirements.

I need a chart (based on data produced every month) to show totals by past quarters and also by any month for the current (not completed) quarter. I have a date table and managed to group current quarters (months outside these groups are ungrouped) but the chart can only be sorted either alphabetically or by ascending/descending number, not chronologically:

I think this is because the group with grouped quarters (Q1 and Q2) and ungrouped months (July and August) is seen as text, and I can't figure out how to concatenate the quarters and months to show chronologically.

This is my date table structure:

Any help will be greatly appreciated.

Re: Chart showing totals by quarters and months of current quarter

lbendlin — Wed, 02 Sep 2020 00:55:57 GMT

What you can try is do a quarter/month hierarchy on the x axis, and then only expand the current quarter. Assuming your month names are sorted by a monthnumber column that should give you the desired outcome.

Re: Chart showing totals by quarters and months of current quarter

amitchandak — Wed, 02 Sep 2020 02:44:37 GMT

pmazz63 , this kind of display is not possible in power bi , You can take both on-axis and expand.

or create a table using summarize and union and use that for display. But table would be static in nature

Re: Chart showing totals by quarters and months of current quarter

Anonymous — Wed, 02 Sep 2020 10:00:28 GMT

For such a setting you have to create a separate static table with the pieces of time you need (just the names), have a column in there that would order them according to your liking and then have a separate measure that would respect selections in such a table. The table would be disconnected and you'd calculate the measure's values via virtual relationships. Basically, you can create any disconnected table you want with any attributes/fields and any hierarchies and then create measures that will work in conjunction with this table and the fact table even without any relationships between them.

Re: Chart showing totals by quarters and months of current quarter

pmazz63 — Thu, 03 Sep 2020 07:34:48 GMT

Thanks lbendlin I tried to do that but I must be doing something wrong as when I expand / drill Q3 (current quarter) the previous quarters disappear:

Re: Chart showing totals by quarters and months of current quarter

Anonymous — Thu, 03 Sep 2020 09:09:30 GMT

pmazz63

To get precisely what you want you have to pay attention to this piece I wrote before:

Basically, you can create any disconnected table you want with any attributes/fields and any hierarchies and then create measures that will work in conjunction with this table and the fact table even without any relationships between them.

Re: Chart showing totals by quarters and months of current quarter

lbendlin — Thu, 03 Sep 2020 12:39:10 GMT

Don't drill in, only right click on Q3 and say "Expand Selection"

Re: Chart showing totals by quarters and months of current quarter

pmazz63 — Thu, 03 Sep 2020 14:20:06 GMT

For some reason I can only see "Expand to next level"

Re: Chart showing totals by quarters and months of current quarter

pmazz63 — Thu, 03 Sep 2020 14:27:55 GMT

Thanks Anonymous I will definitely look at the option you suggested.

Re: Chart showing totals by quarters and months of current quarter

v-xuding-msft — Wed, 16 Sep 2020 08:51:57 GMT

Hi pmazz63 ,

Could you tell me if your problem has been solved? If yes, kindly accept the helpful answer as solution. And welcome to share your own solution. More people who encounter the same issue will benefit from it. If you are still confused about it, please share more details to us.