Forum Discussion

djbuncle's avatar
djbuncle
Icon for Helper I rankHelper I
8 years ago
Solved

Cumulative Sum With Slicing On Another Dim Attribute

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.

 

HourCumul. Deposits Today HourBrand ABrand BBrand C
013,658 013,65813,65813,658
121,805 121,80521,80521,805
228,770 228,77028,77028,770
333,861 333,86133,86133,861
437,610 437,61037,610 
541,227 541,22741,227 
644,006 6 44,00644,006
749,388 749,38849,388 
856,043 856,04356,04356,043
961,830 961,83061,83061,830
1068,192 1068,19268,19268,192
1176,907 1176,90776,90776,907
1278,054 1278,05478,05478,054
1378,054 1378,05478,05478,054
1478,054 1478,05478,05478,054
1578,054 1578,05478,05478,054
1678,054 1678,05478,05478,054

 

Any help would be much appreciated.

Cheers

Need Help
  • djbuncle's avatar
    djbuncle
    8 years ago

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

5 Replies