cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martinrowe
Advocate I
Advocate I

Cumulative Sum of 2 measures based on seperate age calculations

I'm relatively new to PBI, but I have faily solid understand of how it works but I'm struggling to get this to work and cant seem to find anything out there than could help me solve my problem.

 

I have 2 tables, which have a 1:n relationship between them. In their respective tables I have calculated columns with what I will refer to as an "age calculation" which gives me a duration in days between 2 dates which represent timestamps of when the relative events occur.

 

Table 1 the key events are: Application_Date & Paid_Date which i use for the age/duration calculation

Table 2 the key events are: Paid_Date & Fraud_Date which I use to calculate age/duration

 

The events in each table also have a Transaction_Amount which I am able to calculate a measure that gives me the sum of the transactions by the respective durations. I am also able to calculate the cumulative SUM for these amounts over these same durations

 

My problem starts when I try to plot these on the same graph (line and clusters column chart). Depending on which age/duration I use for the shared axis, the two measures based on the other duration doesnt calculate the cumulative amount properly.

 

I assume that this is because the two age/duration calculations are seperate but I am unable to see a way where I can plot them against a "common duration" on the axis. I thought I might beable to create a seperate table for these durations and set up a relationship but that doesnt work as I would need to remove the current 1:n relationship which is used for many other things.

 

Graph.png

 

The green columns and red line here are fine. The black columns should be offset to the right, and normally occur from day 10 onwards and the yellow line should show that cumulative value.

 

I have pasted both cumulative total expressions below.

 

Total Revenue Running Total = 
CALCULATE (
    SUM(Transaction_History[Transaction_Amount]),
    FILTER (ALLSELECTED(Transaction_History), Transaction_History[Days Between App & Transaction] <= MAX (Transaction_History[Days Between App & Transaction])),Transaction_History[Transaction_Type]="sale",Transaction_History[Response_Code]="0"
)

 

Total CB Value Running Total = 
CALCULATE (
    SUM(Fraud_History[Transaction_Amount]),
    FILTER (ALLSELECTED(Fraud_History), Fraud_History[Days Between Transaction & Fraud] <= MAX (Fraud_History[Days Between Transaction & Fraud])), Fraud_History[Fraud_Type] = "chargeback"
)

If I can solve this problem my next step would be to add an addition measure which shows the NET value between these 2 cumulative measures.

 

My overall aim to be able to represent a 'lifetime value' for certain subsets of customer who are deemed "High-Risk"

 

Any help, guidance is appreciated.

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft
Microsoft

Hi @martinrowe,

My problem starts when I try to plot these on the same graph (line and clusters column chart). Depending on which age/duration I use for the shared axis, the two measures based on the other duration doesnt calculate the cumulative amount properly.

Suggestion 1: Rewrite one of the two measures based on the other duration. For example, if you use the duration column from "Transaction_History" table as shared axis, then you can rewrite the "Total CB Value Running Total" measure like below.

Total CB Value Running Total =
VAR currentDuration =
    MAX ( Transaction_History[Days Between App & Transaction] )
RETURN
    CALCULATE (
        SUM ( Fraud_History[Transaction_Amount] ),
        FILTER (
            ALLSELECTED ( Fraud_History ),
            Fraud_History[Days Between Transaction & Fraud] <= currentDuration
        ),
        Fraud_History[Fraud_Type] = "chargeback"
    )
I thought I might beable to create a seperate table for these durations and set up a relationship but that doesnt work as I would need to remove the current 1:n relationship which is used for many other things.

Another suggestion: Create a separate table for these durations and do not set up any relationships with the other two tables. Then rewrite the two formulas like below, and last use the duration column from the new created table as shared axis on the chart.

Total Revenue Running Total =
VAR currentDuration =
    MAX ( NewTable[Days Between App & Transaction] )
RETURN
    CALCULATE (
        SUM ( Transaction_History[Transaction_Amount] ),
        FILTER (
            ALLSELECTED ( Transaction_History ),
            Transaction_History[Days Between App & Transaction] <= currentDuration
        ),
        Transaction_History[Transaction_Type] = "sale",
        Transaction_History[Response_Code] = "0"
    )
Total CB Value Running Total =
VAR currentDuration =
    MAX ( NewTable[Days Between App & Transaction] )
RETURN
    CALCULATE (
        SUM ( Fraud_History[Transaction_Amount] ),
        FILTER (
            ALLSELECTED ( Fraud_History ),
            Fraud_History[Days Between Transaction & Fraud] <= currentDuration
        ),
        Fraud_History[Fraud_Type] = "chargeback"
    )

 

Regards

View solution in original post

2 REPLIES 2
v-ljerr-msft
Microsoft
Microsoft

Hi @martinrowe,

My problem starts when I try to plot these on the same graph (line and clusters column chart). Depending on which age/duration I use for the shared axis, the two measures based on the other duration doesnt calculate the cumulative amount properly.

Suggestion 1: Rewrite one of the two measures based on the other duration. For example, if you use the duration column from "Transaction_History" table as shared axis, then you can rewrite the "Total CB Value Running Total" measure like below.

Total CB Value Running Total =
VAR currentDuration =
    MAX ( Transaction_History[Days Between App & Transaction] )
RETURN
    CALCULATE (
        SUM ( Fraud_History[Transaction_Amount] ),
        FILTER (
            ALLSELECTED ( Fraud_History ),
            Fraud_History[Days Between Transaction & Fraud] <= currentDuration
        ),
        Fraud_History[Fraud_Type] = "chargeback"
    )
I thought I might beable to create a seperate table for these durations and set up a relationship but that doesnt work as I would need to remove the current 1:n relationship which is used for many other things.

Another suggestion: Create a separate table for these durations and do not set up any relationships with the other two tables. Then rewrite the two formulas like below, and last use the duration column from the new created table as shared axis on the chart.

Total Revenue Running Total =
VAR currentDuration =
    MAX ( NewTable[Days Between App & Transaction] )
RETURN
    CALCULATE (
        SUM ( Transaction_History[Transaction_Amount] ),
        FILTER (
            ALLSELECTED ( Transaction_History ),
            Transaction_History[Days Between App & Transaction] <= currentDuration
        ),
        Transaction_History[Transaction_Type] = "sale",
        Transaction_History[Response_Code] = "0"
    )
Total CB Value Running Total =
VAR currentDuration =
    MAX ( NewTable[Days Between App & Transaction] )
RETURN
    CALCULATE (
        SUM ( Fraud_History[Transaction_Amount] ),
        FILTER (
            ALLSELECTED ( Fraud_History ),
            Fraud_History[Days Between Transaction & Fraud] <= currentDuration
        ),
        Fraud_History[Fraud_Type] = "chargeback"
    )

 

Regards

Thank for the great feedback. I went with your first suggestion, but in doing so created a second version of the measure as the original one was useful the way it was in other areas.

 

Though a the first suggestion does get me to my end point now, It does feel a little bit like a fudge in the context of the wider data model I have, so i'm going to continue to look at your 2nd suggestion to see if there is a way I can make this work for this and other similar calculations/visulations I have

 

Thanks again - Martin

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.