cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
djbuncle Regular Visitor
Regular Visitor

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

Accepted Solutions

Re: Aggregated Value Wierd Beahviour

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

Re: Aggregated Value Wierd Beahviour

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 Regular Visitor
Regular Visitor

Re: Aggregated Value Wierd Beahviour

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.

Re: Aggregated Value Wierd Beahviour

Is this the result you want to achieve?

 

Capture.PNG

djbuncle Regular Visitor
Regular Visitor

Re: Aggregated Value Wierd Beahviour

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

Re: Aggregated Value Wierd Beahviour

I guess this should be it, right?

 

Capture.PNG

djbuncle Regular Visitor
Regular Visitor

Re: Aggregated Value Wierd Beahviour

Thats the one! Please enlighten me.

Re: Aggregated Value Wierd Beahviour

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

djbuncle Regular Visitor
Regular Visitor

Re: Aggregated Value Wierd Beahviour

Champion. Much appreciated.

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 558 members 4,382 guests
Please welcome our newest community members: