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
djbuncle
Helper I
Helper I

Aggregated Value Wierd Beahviour

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!

 

Global Deposit Calc.png

 

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

1 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 )
    )

View solution in original post

8 REPLIES 8
avanderschilden
Resolver I
Resolver I

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?

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.

Is this the result you want to achieve?

 

Capture.PNG

Thats the one! Please enlighten me.

here you go;

 

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

Champion. Much appreciated.

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

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;

 

DepositsGlobal.png

I guess this should be it, right?

 

Capture.PNG

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.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors