Answered by:
Complex running total problem
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 PMAnswerer
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 SupportTuesday, 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 PMAnswerer 
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