Forum Discussion

kwahila's avatar
kwahila
Regular Visitor
8 years ago

Column Total for Measure

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!!

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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's avatar
      kwahila
      Regular Visitor
      8 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        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