cancel
Showing results for
Did you mean:
Highlighted
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!

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

1 ACCEPTED SOLUTION

Accepted Solutions
Member

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 )
)```
8 REPLIES 8
Member

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?

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.

Member

Re: Aggregated Value Wierd Beahviour

Is this the result you want to achieve?

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;

Member

Re: Aggregated Value Wierd Beahviour

I guess this should be it, right?

Regular Visitor

Re: Aggregated Value Wierd Beahviour

Thats the one! Please enlighten me.

Member

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

Re: Aggregated Value Wierd Beahviour

Champion. Much appreciated.

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

Announcements

Challenge: Can You Solve These?

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

Community News & Announcements

Get your latest community news and announcements.

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.

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
Recent signins: