RunningSum() sorted by Classification()

[Former Member]

Hi folks,

I have got 4 cars (A, B, C and D) and I want to calculate the running sum sorted by descending Cost as in the column D. Unfortunately this is easy if the column Cost is sorted in the table but I need the table sorted only by the dimension as in the image:

I need this special RunningSum to know if the car is taking part in the 50% most expensive cars (Yes if the sorted running sum is lower or equal than the 50% ot the total running sum )

I hope my explanation and the image are enogh to be understood.

Thanks in advance,

Jonás

[Former Member · Accepted solution]

Hi Jonás,

try this :

create a dimension variable [cost_dim] = 0-[cost];

create a measure variable  [runningsumcost] = Previous(Self ) In ([cost_dim])+[cost],

Use [runningsumcost] in your Report.

Let me know if it worked for you.

Regards,

Rogerio

[Former Member · Accepted solution]

Awesome.

It worked for me creating [runningsumcost] as a detail of [cost_dim]

Thanks,

Jonás

[Former Member · Accepted solution]

It happens that before the dimension Car I have other two dimensions (Brand and Country) which makes breaks in the same table.

I am trying to modify [cost_dim] and [runningsumcost] to work properly with those dimensions but I am not succeding.

Could you show me how to modify the variables with those dimensions?

Thanks,

Jonás

[Former Member · Accepted solution]

Hi Jonás,

I´m strugling with it myself. Can you share a piece of your data (like you did in the picture) but as a txt or excel file, as well as a printscreen of how you expect your data to be?

Regards,

Rogerio

[Former Member · Accepted solution]

Hi Rogerio,

I have no data to share, in fact, this is a silly example of the real problem but you hit with the behaviour of the variables. However I can show how the table looks like with Brand and Country with breaks.

I need to classify the car D as in the first half (0 to 5.5) of the cars bought.

Thanks for all your support,

Jonás

[Former Member · Accepted solution]

Hi,

Can you please elaborate a little bit more ? Why is car D in the first half ?

Regards,

Rogerio

[Former Member · Accepted solution]

First, I would sort the Cost measure in descendant order so the first row has the most expensive car.

Second, I would calculate the running sum to get the contribution between several cars in descendant order.

Having the Cost total which is 11, I want to know the cars which have contributed in the total cost the first 50% from 0 to 5.5. In this small example the first car covers almost the 50% of the total cost so I will classify as "In the first 50%"

As I said before, without Brand and Country dimensions in the table, your variables work perfectly. But I need the table sorted by the car name and with breaks for Brand and Country.

For example, after adding Country, Previous(Self) is not calculated and the running sum is equal to the cost.

Thanks

Jonás

[Former Member · Accepted solution]

Hi,

try

RunningSum([cost]) In [cost_dim])

Regards,

Rogério

[Former Member · Accepted solution]

Beautiful.

As in the table I have got Brand and Country from another data provider, I finally added Brand and Country from the data provider which has Cost and after merging the dimensions the running sum appeared correctly.

Again, thanks very much for all your support

Jonás

[former_member741439]

There is a similar requirement in my project, I want to apply running sum (measure) where measure should be sorted in descending order. I don't want to alter table structure and also measure consist of duplicate values.

Please help me for getting this. Thanks in Advance.