Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AlexanderNN
Frequent Visitor

Sorting when using calculation groups

Hi all,

I'm trying to order my column charts based om my measure that sums one of my columns in my model. I'm using calculation groups for all my Time Intelligence and this seems to cause some issues for me. Here's an example of the visual I want sorted:

AlexanderNN_3-1649751119054.png

 And this works just as intended when I have a single value selected for my Time Intelligence calculation group as shown below:

AlexanderNN_1-1649750994460.png

However, I want to show YTD values and YTD PY in the same visuals so it's easy for my users to compare this year's sales vs last year. This is where my issue becomes evident. Using the same values to Sort By I get the following:

AlexanderNN_4-1649751206826.png

 With two values selected in my Calculation Group

AlexanderNN_5-1649751247309.png

This becomes very messy, and hard for users to grasp what I'm trying to show. What I want to show is both YTD and YTD PY and the values should be sorted by YTD's values for the selected measure.

 

Is there any way to fix this? Thanks in advance 🙂 

 

 

6 REPLIES 6
Stoppelaar
Frequent Visitor

Hi, 

Dont know if you solved this but I did a little "workaround" and named the Calculation items 1,2,3 etc:

(Sorry for the danish)

Stoppelaar_0-1673869391079.png

Then used a MID to get the texted I wanted and used that in the reports and it's working fine.

Stoppelaar_1-1673869446181.png

Regards,

Andreas

 

KPINC
New Member

Hi @AlexanderNN 

 

I am having the exact same issue with 2 calculation items : YTD and YTD%.

 

When I put the calculation group in a matrix (column), and select only one of the 2 items, I do not have any issue when sorting.

 

But once I select the 2 items (or any other item associate with one of those 2 items => I tried with a dummy item = 0), the sorting is not working properly. Sorting seems random.

 

I tried to do the same thing with 2 other calculated items => CY (which is simply "selectedmeasure()") and CY%. There is no problem with those 2, the sorting is good even when I selected both items.

 

In fact, the sorting that seems random with the YTD items, is in fact the sorting of the CY => as if when selecting 2 items with YTD, the sorting goes by default to the initial "selectedmeasure()".

 

The issue seems to be related to the YTD calculation, but I can not figure out why. 

 

I looked for the same problem in many forums, but couldn't find anyone encountering the same issue...except for you.

 

Did you finally find where the problem was coming from ? How did you resolve it ?

 

Would be great if we could resolve this.

 

Regards

amitchandak
Super User
Super User

@AlexanderNN , Add a YTD measure as tooltip and try to sort on that

@amitchandak That would certainly work, but I would rather avoid it if possible. I have all my measures in one measure table and all my Time Intelligence functions in a calculation group and I would not like to introduce any more measures. 

But if it isn't any other way I probably will resort to this. I'm wondering if there is any built-in functionality in Power BI to handle sorting when using calculation groups.

This is an easy one to fix.  Just like any table in your model you can specify columns to sort by other columns.  In this case you merely need to sort your measures by the Ordinal column.  Not sure why Microsoft isn't doing this for us, but just go to the table view table tab and click on your calculation group (table) select the first column and then click sort by column and pick the Ordinal column.  See attached.SortIssueSolved.PNG

Shishir22
Solution Sage
Solution Sage

Hi @AlexanderNN ,

 

Can you please share pbix after hiding sensitive data?

 

Cheers,

Shishir

Cheers,
Shishir

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors