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

Grouping values into more than one group

I'm hoping there is a simple solution to this:

 

I've created a column chart with months in the x-axis and a total column in the y-axis. I now want to display multiple year-to-date (YTD) columns along the x-axis rather than individual months. For example, I'd like to see Feb YTD, Mar YTD, Apr YTD, May YTD, ..., Dec YTD on the x-axis. Groups seems to be the answer to this problem, but it looks like I can only add an ungrouped value to one group, not multiple groups. For example, I can only add January and February to the Feb YTD group and not to another group like Mar YTD. Essentially, it seems that values and groups are a 1-to-1 relationship, instead of a 1-to-many relationship.

Does someone know how to allow values to be grouped into multiple groups? Or is there some DAX expression for what I'm trying to do?

Thank you for your help!

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Grouping values into more than one group

By default, the engine does not handle n:n relationships, so DAX will be required to do a YTD calculation.

 

If you use April release, you may want to try the new quick measures feature preview:

https://powerbi.microsoft.com/en-us/blog/quick-measures-preview/

 

If you need more help, don't hesitate to ask here (with a description of your model).

 

4 REPLIES 4
Super User
Super User

Re: Grouping values into more than one group

Hi @christopheralan,

For doing this you should calculate a measure with TOTALYTD then used that as your values in the visuals.

Example: YTDMEASURE = TOTALYTD (SUM(Table[columnvalue]), table1[date])

Use the date column as your x-axis, there is also available the QTD and MTD calculation.

Regards,
Mfelix


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

Proud to be a Datanaut!




bblais Member
Member

Re: Grouping values into more than one group

Assuming you have a calendar table, you can first create a measure to SUM up your sales, or in my example Revenue

 

Revenue = SUM(Sales[Order Revenue])

Then just create a TOTALYTD measure that calculates revenue YTD based on your sale date or order date or whatever date:

Revenue YTD = TOTALYTD([Revenue],'Calendar'[Date])

Then if you group up your calendar date by month:

Capture.PNG

 

 

 

and make it your axis of your column chart, and make Revenue YTD your value, it should look like this:

 

Capture2.PNG

 

 

 

 

Re: Grouping values into more than one group

By default, the engine does not handle n:n relationships, so DAX will be required to do a YTD calculation.

 

If you use April release, you may want to try the new quick measures feature preview:

https://powerbi.microsoft.com/en-us/blog/quick-measures-preview/

 

If you need more help, don't hesitate to ask here (with a description of your model).

 

christopheralan Frequent Visitor
Frequent Visitor

Re: Grouping values into more than one group

Thanks, LaurentCouartou!  Quick measures worked Smiley Happy.