Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to calculate "Share of Profit", the percent of Total Profit contributed by a particular Product (my dimension). I have many expressions that calculate each part of the Profit formula. In the end, I want to calculate (Profit from that Product) / (Profit total).
I know I can reference other expressions by typing the name of the column, but is there a way to reference the Total Expression value? I was hoping [Profit].total or [Total Profit] or sum([Profit]) would work, but that doesn't seem to be working.
I've attaced a simplified example in Excel to show what I'm doing. What is the easiest way of typing the formula for the yellow cells, without copying all expressions in the different Expense steps?
Thanks for any help in advance!
Jonathan, thanks for the note on All vs. {1} TOTAL. That's not the question in this thread, but is good to know. The question is, can you reference the Total value on an Expression in a different Expression in an easy way?
I figured out what I needed to do. For Profit Share, copy the same expression that is in Profit. Then, on the Expression tab next to check the Relative box (right below the Enable check box). This will calculate % of Total.
You can also do it differently. Use sum(Profit) / sum(TOTAL Profit) and then go to the number format and click show in %. Check the attached sample file.
Regards,
Sajeevan