Column Total for Measure

[kwahila]

Hello, I am probably asking a basic questions but regardless, after looking at different post I can not figure out the solution. I have a single dataset to which I have created several measures. 

At the lowest level, my data consists of itemized travel expenses for people in an organisation. I aggregate the data based on their functional unit. By this, I have measures to calculate

• total expenses in each year
• baseline (average of 2014/15)
• percent increase (16 with respect to baseline)
• target, using and IF/THEN statement and Sum statement.

The story is that the measure is correct for each line, but the column grand total is not the sume of the individual lines. I would expect this, but rather it seems that the total represents an evaluation of the equation at the highest level. 

• Example of how I calculate yearly expenses:

Expense 16 =
CALCULATE(
SUM('TravelExpenseData'[ExpenseAmountEuro]);
YEAR('TravelExpenseData'[ExpenseDate]) IN { 2016 }
)

• Baseline

Expense Baseline = (TravelExpenseData[Expense 14]+TravelExpenseData[Expense 15])/2

• Increase

Expense % Incr = IFERROR(([Expense 16]-[Expense Baseline])/[Expense Baseline];5 )

• Target

ExpenseTarget(1) = if([Expense 16]<[Expense Baseline];[Expense 16];if([Expense % Incr]<0,1335;[Expense Baseline];(0,85*[Expense 16]+[Expense Baseline]*1,1335)/2))

Here is the data:

Total should be €4,947

Thanks in advance!!

[Ashish_Mathur]

Hi,

Why should the total be 4,947?  Also, from the formulas you have written, it looks like you have one column for each year.  if my contention is correct, then we can unpivot your data before writing formulas.  That way your formulas will become simpler as well.

Please share the link from where i can download your file.

[kwahila]

Hello,

Each line represents the target for a different functional area. The grand total as caluclated by Power BI does not equal the sum of each line. This is what is bizzare to me.

	ExpenseTarget
Group 1	€ 1 830,92
Group 2	€ 26 745,18
Group 3	€ 282 477,97
Group 4	€ 381 397,28
Group 5	€ 84 534,92
Group 6	€ 203 550,51
Group 7	€ 136 677,53
Group 8	€ 110 434,64
Group 9	€ 1 971 366,35
Group 10	€ 1 218 677,30
Group 11	€ 31 129,50
Group 12	€ 498 550,79
Power BI Result	€ 4 936 613,54
Sum of each value	€ 4 947 372,89

I cannot share the file, but nevertheless i have included all of the formulas.

Thanks!

Kevin

[v-huizhn-msft]

Hi @kwahila，

Please try the following formula to create the measure to get ExpenseTarget, and check if it works fine.

ExpenseTarget =
SUMX (
    ExpenseTarget,
    CALCULATE (
        IF (
            [Expense 16] < [Expense Baseline],
            [Expense 16],
            IF (
                [Expense % Incr] < 0.1335,
                [Expense Baseline],
                ( 0.85 * [Expense 16]
                    + [Expense Baseline] * 1.1335 )
                    / 2
            )
        )
    )
)

Best Regards,
Angelia

[kwahila]

Hello Angelina,

Thanks for the suggestion. It won't (doesn't) work because my table "ExpenseTarget" (actually called "TravelExpenseData") is not a single line for each functional area but a full list of every single travel expense for every traveler. Entries include a full organisational path. I am aggregating the total expenses in the visualisation, not in the query. I need to keep this granularity for deep diving; in my report I want to be able to show the targets at different levels of the organisation. Is this possible?

Thank you,

Kevin

[v-huizhn-msft]

Hi @kwahila,

I can't imagine what your want, € 4 947 372,89 is the sum of twelve Groups based on my understanding. And each group's value is calculated from the measure "ExpenseTarget(1)".

Best Regards,
Angelia