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