Cumulative Sum With Slicing On Another Dim Attribute

[djbuncle]

Hi.

I have an issue whereby I've created a running total measure, however I can't slice it on an attribute from another linked dimension.

Cumulative Deposits Today:= CALCULATE([Deposit Value], FILTER(ALLEXCEPT(Deposits, Deposits[PlayerId]), Deposits[DepositHour]<= MAX(Deposits[DepositHour])), FILTER('Activity Date',[Today]=TRUE()))

Where the PlayerId is the key to a dimension with attributes I want to slice on.

The behaviour is such that any attribute I slice-on form the other dimension gets the same value.

On the left shows the calculation working properly. Then on the right we see where I'm slicing on brand which is an attribute from the dimension I link to via the PlayerId I've included in the ALLEXCEPT function. All brands get the same value whereas they each should make up a different proportion of the total.

Hour	Cumul. Deposits Today		Hour	Brand A	Brand B	Brand C
0	13,658		0	13,658	13,658	13,658
1	21,805		1	21,805	21,805	21,805
2	28,770		2	28,770	28,770	28,770
3	33,861		3	33,861	33,861	33,861
4	37,610		4	37,610	37,610
5	41,227		5	41,227	41,227
6	44,006		6		44,006	44,006
7	49,388		7	49,388	49,388
8	56,043		8	56,043	56,043	56,043
9	61,830		9	61,830	61,830	61,830
10	68,192		10	68,192	68,192	68,192
11	76,907		11	76,907	76,907	76,907
12	78,054		12	78,054	78,054	78,054
13	78,054		13	78,054	78,054	78,054
14	78,054		14	78,054	78,054	78,054
15	78,054		15	78,054	78,054	78,054
16	78,054		16	78,054	78,054	78,054

Any help would be much appreciated.

Cheers

[Greg_Deckler]

Sample data would help tremendously.  Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[djbuncle]

Hey Greg. I've added some data in there now... hopefully that makes it a bit clearer.

[v-juanli-msft]

Hi @djbuncle

You data model is like the following?

Best Regards

maggie

[djbuncle]

Here is a simplified image of the data model, where I'm trying to do a cumulative sum of the [DepositAmount] (over the [DepositHour] of the current day). But the slicing by [Brand] from the Player dimension doesn't work properly.

Note I can create a specific measure that filters on the brand, but if I create a generic measure and try to slice in a pivot table, the brands all return the same amount (i.e. it doesn't slice).

[djbuncle · Accepted solution]

For the record, I solved this one.

I presumed the field to include in the ALLEXCEPT function would be the key to the appropriate dimension (Deposits[PlayerId]) for which I wanted to slice on.

Cumulative Deposits Today:= CALCULATE([Deposit Value], FILTER(ALLEXCEPT(Deposits, Deposits[PlayerId]), Deposits[DepositHour]<= MAX(Deposits[DepositHour])), FILTER('Activity Date',[Today]=TRUE()))

However what was required was the actual attribute from the related dimension (Player[Brand]). So the downside to this is that you have to list every attribute you want to be able to slice on.

Cumulative Deposits Today:= CALCULATE([Deposit Value], FILTER(ALLEXCEPT(Deposits, Player[Brand]), Deposits[DepositHour]<= MAX(Deposits[DepositHour])), FILTER('Activity Date',[Today]=TRUE()))