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

    • kwahila's avatar
      kwahila
      Regular Visitor
      8 years ago

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

        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