locked
Complex running total problem RRS feed

  • Question

  • Typically we need to compare 2 versions of figures to analyse different types of factors causing the revenue and cost to differ and most importantly the margin and margin%.

    For example I have a fact table:

    Type Category Amt Baseline Ver Current Ver
    New Revenue 1000 0 1000
    New Cost 500 0 500
    Attrition Revenue -800 800 0
    Attrition Cost -500 500 0
    Stable Revenue 0 100 100
    Stable Cost 0 80 80

    It shows a "New" employee added 1000 revenue to baseline revenue of 0 resulting current revenue of 1000 but added cost of 500.  Another employee attrition causing losing 800 revenue and reduced 500 cost.  The rest of the team baseline revenue 100 and cost 80.

    I have a few dimension tables to facilitate the analysis, category dimension to calculate the GM and GM%:

    Category Sort
    Revenue 1
    Cost 2
    GM 3
    GM% 4

    and type table to calculate the different factor contributions:

    Type Sort Calc_Type
    Baseline 1 End
    New 2 Change
    Attrition 3 Change
    Current 4 End

    I have added the following measures:

    1. type_amt calculates the baseline, current or factors depending on the type displayed.

    type amt:=IF (
        HASONEVALUE ( 'type'[Type] ),
        SWITCH (
            VALUES ( 'type'[Type] ),
            "Baseline", CALCULATE ( SUM ( data[Baseline Ver] ), ALL ( 'type'[Type] ) ),
            "Current", CALCULATE ( SUM ( data[Current Ver] ), ALL ( 'type'[Type] ) ),
            SUM ( data[Amt] )
        )
    )

    Revenue:=[type amt](category[Category]="Revenue")

    GM:=[Revenue]-[type amt](category[Category]="Cost")

    Revenue Baseline:=[Revenue]('type'[Type]="Baseline")

    GM Baseline:=[GM]('type'[Type]="Baseline")

    2. GM% calculates the margin% if type=baseline or current, otherwise calculate the margin% different from baseline margin%

    GM%:=IF (
        HASONEVALUE ( 'type'[Calc_Type] ),
        IF (
            VALUES ( 'type'[Calc_Type] ) = "End",
            [GM] / [Revenue],
            ( [GM Baseline] + [GM] )
                / ( [Revenue] + [Revenue Baseline] )
                - [GM Baseline] / [Revenue Baseline]
        )
    )

    3. Finally, to display the value I created a category amt measure:

    category amt:=IF (
        HASONEVALUE ( category[Category] ),
        SWITCH (
            VALUES ( category[Category] ),
            "Revenue", [type amt],
            "Cost", [type amt],
            "GM", [GM],
            [GM%]
        )
    )

    4. In addition, I have created relationships between fact table and dimension tables:

    Active Table Related Lookup Table
    Yes data [Type] type [Type]
    Yes data [Category] category [Category]

    In the end, I have the report I want, it shows how revenue, cost, GM changes from baseline to current, it also shows the GM% differential from the baseline GM% contributed by different factors:

    Row Labels category amt
    Revenue
    Baseline 900
    New 1000
    Attrition -800
    Current 1100
    Cost
    Baseline 580
    New 500
    Attrition -500
    Current 580
    GM
    Baseline 320
    New 500
    Attrition -300
    Current 520
    GM%
    Baseline 0.355555556
    New 0.076023392
    Attrition -0.155555556
    Current 0.472727273

    but when I added a running total measure to calculate the running total within each category for plotting waterfall graph:

                               

    running_total :=
    IF (
    HASONEVALUE ( 'type'[Type] ),
    SUMX (
    FILTER ( ALL ( 'type' ), 'type'[Sort] <= MAX ( 'type'[Sort] ) ),
    [category amt]
        )
    )

    It is not showing what I expected (it does not add properly in baseline and current rows):

    Row Labels category amt running_total
    Revenue 200
    Baseline 900 0
    New 1000 1000
    Attrition -800 200
    Current 1100 200
    Cost 0
    Baseline 580 0
    New 500 500
    Attrition -500 0
    Current 580 0
    GM 200
    Baseline 320 0
    New 500 500
    Attrition -300 200
    Current 520 200
    GM% #NUM!
    Baseline 0.355555556 #NUM!
    New 0.076023392 #NUM!
    Attrition -0.155555556 #NUM!
    Current 0.472727273 #NUM!

    I want to show the following report:

    Row Labels category amt running_total
    Revenue    
    Baseline 900 900
    New 1000 1900
    Attrition -800 1100
    Current 1100 2200
    Cost    
    Baseline 580 580
    New 500 1080
    Attrition -500 580
    Current 580 1160
    GM    
    Baseline 320 320
    New 500 820
    Attrition -300 520
    Current 520 1040
    GM%    
    Baseline 0.355555556 0.355555556
    New 0.076023392 0.431578947
    Attrition -0.155555556 0.276023392
    Current 0.472727273 0.748750665

    Any help on the running total measure?





    • Edited by williamwong Saturday, January 23, 2016 2:50 AM
    Friday, January 22, 2016 2:13 PM

Answers

  • It's quite hard to see what you're doing, and I think you're making things to complex. The first step would be to simplify your formulas.

    For instance,

    running_total :=
    IF (
    HASONEVALUE ( 'type'[Type] ),
    SUMX (
    FILTER ( ALL ( 'type' ), 'type'[Sort] <= MAX ( 'type'[Sort] ) ),
    [category amt]
        )
    )

    can be rewritten as

    running_total:= SUMX(FILTER(ALL('type'), 'type'[Sort]<= MAX('type'[Sort])), [category amt]

    as this doesn't rely on a single value for 'type'[Type]. In fact, you can get rid of all IF(HASONEVALUE(... statements by changing your tables a bit. If you change the Sort column in Category from 1,2,3,4 to 1,2,4,8 you can rewrite [category amt] as

    category amt:=
        SWITCH (
            SUM ( category[Sort] ),
            1, [type amt],
            2, [type amt],
            4, [GM],
            8, [GM%]
        )

    You do a lot of magic with filters, removing filters and reapplying filters implicitly and I feel the existence of the relationships may cause unexpected behavior. When removing the relationship to the Type table, and changing the Sort column in Type from 1,2,3,4 to 1,2,4,8, the [type amt] measure could be rewritten as

    Total Baseline:=SUM(data[Baseline Ver])

    Total Current:=SUM(data[Current Ver])

    Amt New :=CALCULATE(data[Amt], data[Type] = "New")

    Amt Attrition := CALCULATE(data[Amt], data[Type] = "Attrition")

    type amt:=
        SWITCH (
            SUM ( 'type'[Sort] ),
            1, [Total Baseline]
            2, [Amt New],
            4, [Amt Attrition],
            8, [Total Current]
    )

     Next, put Type and Category in a pivot table and see what [type amt] and [category amt] do.

    • Marked as answer by williamwong Wednesday, January 27, 2016 3:47 AM
    • Unmarked as answer by williamwong Wednesday, January 27, 2016 6:52 AM
    • Marked as answer by williamwong Wednesday, January 27, 2016 7:13 AM
    Tuesday, January 26, 2016 5:39 PM
    Answerer

All replies

  • Hi,

    Thank you for your question. 

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, January 26, 2016 8:40 AM
  • It's quite hard to see what you're doing, and I think you're making things to complex. The first step would be to simplify your formulas.

    For instance,

    running_total :=
    IF (
    HASONEVALUE ( 'type'[Type] ),
    SUMX (
    FILTER ( ALL ( 'type' ), 'type'[Sort] <= MAX ( 'type'[Sort] ) ),
    [category amt]
        )
    )

    can be rewritten as

    running_total:= SUMX(FILTER(ALL('type'), 'type'[Sort]<= MAX('type'[Sort])), [category amt]

    as this doesn't rely on a single value for 'type'[Type]. In fact, you can get rid of all IF(HASONEVALUE(... statements by changing your tables a bit. If you change the Sort column in Category from 1,2,3,4 to 1,2,4,8 you can rewrite [category amt] as

    category amt:=
        SWITCH (
            SUM ( category[Sort] ),
            1, [type amt],
            2, [type amt],
            4, [GM],
            8, [GM%]
        )

    You do a lot of magic with filters, removing filters and reapplying filters implicitly and I feel the existence of the relationships may cause unexpected behavior. When removing the relationship to the Type table, and changing the Sort column in Type from 1,2,3,4 to 1,2,4,8, the [type amt] measure could be rewritten as

    Total Baseline:=SUM(data[Baseline Ver])

    Total Current:=SUM(data[Current Ver])

    Amt New :=CALCULATE(data[Amt], data[Type] = "New")

    Amt Attrition := CALCULATE(data[Amt], data[Type] = "Attrition")

    type amt:=
        SWITCH (
            SUM ( 'type'[Sort] ),
            1, [Total Baseline]
            2, [Amt New],
            4, [Amt Attrition],
            8, [Total Current]
    )

     Next, put Type and Category in a pivot table and see what [type amt] and [category amt] do.

    • Marked as answer by williamwong Wednesday, January 27, 2016 3:47 AM
    • Unmarked as answer by williamwong Wednesday, January 27, 2016 6:52 AM
    • Marked as answer by williamwong Wednesday, January 27, 2016 7:13 AM
    Tuesday, January 26, 2016 5:39 PM
    Answerer
  • You have suggested one problem on the relationship between type and data. 

    I suspect the problem lies with SUMX, when it iterates to type[Type]="New" and when it tries to evaluate [category amt](category[Category]="GM%") the formula uses is:

     ( [GM Baseline] + [GM] )
                / ( [Revenue] + [Revenue Baseline] )
                - [GM Baseline] / [Revenue Baseline]

    while the engine can evaluate [GM] and [Revenue], it cannot evaluate [Revenue Baseline] and [GM Baseline] which requires type[Type]="Baseline" and it is currently set to "New" because of the iteration. 

    I have made 2 changes:

    1. remove the relationship between type and data

    2. remove the dependency of baseline calculation on type

    Total Baseline := SUM(data[Baseline Ver])

    Total Current:=SUM(data[Current Ver])

    Revenue Baseline := [Total Baseline](category[Category]="Revenue")

    GM Baseline:=[Revenue Baseline]-[Total Baseline](category[Category]="Cost")

    type amt:=IF (
        HASONEVALUE ( 'type'[Type] ),
        SWITCH (
            VALUES ( 'type'[Type] ),
            "Baseline", [Total Baseline],
            "Current", [Total Current],
            CALCULATE(SUM ( data[Amt] ),data[Type]=VALUES('type'[Type]))
        )
    )

    Now it works!

    Wednesday, January 27, 2016 7:25 AM