Order by First Row

Questions & Answers
1

Hi everybody!

I have table with two dimensions: “Impacted Features” and Time. The time dimensions is also a page, in order for it to be selectable.

The problem is: I want to order the table by “Impacted Feature”. When I try to sort itthe impacted features get jumbled as only Time is sorted:

image
image816×324 32.5 KB

(see how the “Impacted Feature” “CM: Search and filters” is repeated by quarter)

Ss I want to keep the agregation by “Impacted Feature”, like this:

image
image817×626 48.1 KB

while ordering the sum of it’s values.

How do I do it?

Thanks!

2

Hi,

As you need to keep the aggregation of the Impacted feature, I assume that ordering would be needed within the aggregated rows of the “Impacted feature”. For that, you can create a custom measure for ordering. The measure would concatenate the name of the first dimension and the amount of the measure. Then you can apply ordering on that measure and remove it from the report for keeping the report clean and preserving the order.

In your case, the custom measure for ordering would be the following:

[Impacted feature].CurrentMember.Name
||
Format(CoalesceEmpty([Measures].[Issues resolved],0),"0000")

The report would look like this (I used the Assignee dimension in my example):

image
image1228×1502 81.9 KB

Kindly,
Janis, eazyBI support

3

Hi!
Thanks for he reply, but this doesn’t solve the problem. I’l try to explain it better.

With the measure you proposed we have the following:

image
image1043×736 74.2 KB

Which doesn’t give me the ordering by the sum of the impacted level values.

What I wat is, in the image above, for example, ordering by descendent, would give me:

  • 1st Impact level: “CM: Search + Filters” (as the sum 7 + 4 = 11 the value of both Q3 2020 + Q2 2020)
  • 2nd Impact level: “Other” (as the sum 6 + 2 is 8 = the value of both Q3 2020 + Q2 2020)
  • 3rd Impact level: “CM: Rules” (as the sum 6 + 0 is 6 = the value of both Q3 2020 + Q2 2020)

Is there a way to change the calculated metrics in order to have this type of ordering?

Thanks!

4

Hi,

Thank you for the update.
Yes, it is possible to create such ordering either. We can avoid the concatenation in the formula in this case.

Please, use the following formula for the ordering measure:

([Measures].[Issues resolved],
 [Time].CurrentHierarchy.DefaultMember)

This formula gives the total for all quarters in each row for the Impacted feature.

Kindly,

5

Thanks for the reply.
I think it’s almost there :slight_smile:

The problem now is that the proposed metric gives us the sum of all quarters. and I only want the sum of the quarters selected (time is a row and a page)

For example:

image
image863×303 24.2 KB

The sum of “CM: Search + Filters” should be 11 (sum of two quarters), but it’s 43 (sum of all quarters). And that value should change as we select more quarter.

Value in the image below should be the sum of the 3 quarters (14):

image
image853×271 18.9 KB
.

I tried to do this:
{code}
([Measures].[Issues resolved],
[Time].CurrentHierarchy.SelectedMember)
{code}
But the “SelctedMember” isn’t valid: what the correct way do to it?

Thanks!

6

Right, the ordering by the selected quarters is a bit more tricky.

Unfortunately, it seems not possible to create the formula which automatically adjusts to the periods selected on the report pages. You need to create the ordering measure duplicating the selection in the formula:

Sum(
{[Time].[Quarter].[Q1 2020],[Time].[Quarter].[Q2 2017],[Time].[Quarter].[Q3 2020]},
[Measures].[Issues resolved]
)

Kindly,
Janis eazyBI support

7

That’s pretty disappointing :\

Do you know if this type of Feature is currently being planed or exists in the latest versions?