Aggregated Value Wierd Beahviour

[djbuncle]

I'm having trouble understanding why this certain calculation doesn't work for me.

In my example, the [Global Id] is the parent id and the [Player Id] is the child. My aim was to create a calculation which will show the total deposit value aggregagted over the global id and this is to work when slicing on any of the children.

Deposit Value (Global):= CALCULATE([Deposit Value], FILTER(ALL('Deposits'), [PlayerGlobalId] = SELECTEDVALUE(Player[Global Id])))

Pivot (1) shows the value I'm hoping to get from this calculation.

Pivot (2) shows those individual children

Then pivot (3) shows me filtering on the child which has the deposit value of 20, but the global deposit amount it shows is that of the other child and doesn't include the current!

It's baffling to me, any suggestions would be appreciated.

[avanderschilden]

Hello,

Based on your explanation I don't understand why you use "FILTER(ALL('Deposits'), [PlayerGlobalId] = SELECTEDVALUE(Player[Global Id])))".

If you select a PlayerID, the measure will only return the deposit value of the selected PlayerID, so why are you removing the filter from 'Deposits' with the ALL function?

[djbuncle]

Hi.

I'm using ALL because if the current context is the PlayerId (child), then I need to somehow unfilter the dataset to be able the calculate the deposits over the siblings (linked accounts via the GlobalId) as well.

[avanderschilden]

Is this the result you want to achieve?

[djbuncle]

Thats the one! Please enlighten me.

[avanderschilden · Accepted solution]

here you go;

Measure = 
VAR GlobalIDs =
    VALUES ( 'Table'[Global_ID] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Global_ID] IN GlobalIDs )
    )

[djbuncle]

Champion. Much appreciated.

I'll have to get my head around VALUES vs SELECTEDVALUE.

[djbuncle]

Not quite. What I'm trying to do is slice on the Player_ID, but show the value of the combined deposits (linked via the Global_Id). So your pivot on the right would like like this;

[avanderschilden]

I guess this should be it, right?