Expression to get correct sum value

[#1]

Hello community,

I'm trying to get the sum of costs for a user based on the ID and the company.

Here's my example script which return the wrong sum as you can see in the screenshot.

So the correct output would be 2 for User A in company X and 4 for user B in company Y

The problem is the ChangeID which creates additional rows for each ID.

LOAD * INLINE [

    ID, User, ChangeID, Cost, Comany

    1, A, 1, 2, X

    1, A, 2, 2, X

    1, A, 3, 2, X

    2, B, 1, 4, Y

    2, B, 2, 4, Y

];

Can you show me how this can be done?

Thank you.

[pamaxeed · Accepted solution]

Try:

Sum(Aggr(Sum(distinct Cost), User))

[rubenmarin]

Hi Thorsten, one easy solution is using Avg() instead of Sum(), can this work on your complete model?.

For working also in totals you can use:

Sum(Aggr(Avg(Cost), User))

[nagaiank]

Use User as dimension and

Aggr(FirstSortedValue(Cost,-ChangeID),User) as expression

[MK_QSL]

SUM(DISTINCT Cost)

or

SUM(Cost)/Count(Comany)

or

AVG(Aggr(SUM(Cost),Comany,ChangeID))

[#6]

Hey guys,

thank you for your answers I had to make a slight modification to get it to work for me.

I also added a few more lines to the data.

In the screenshot you can see that in the table box the sum of User A is 8 over all companies.

Sum(Aggr(Sum(distinct Cost), ID)) did the trick for me.

LOAD * INLINE [

    ID, User, ChangeID, Cost, Comany

    1, A, 1, 2, X

    1, A, 2, 2, X

    1, A, 3, 2, X

    2, B, 1, 4, Y

    2, B, 2, 4, Y

    3, A, 8, 2, Y

    3, A, 9, 2, Y

    3, A, 10, 2, Y

    3, A, 11, 2, Y

    4, A, 55, 4, Y

    4, A, 56, 4, Y

];

Thank you,

Thorsten