• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic calculation of measure based on User Input

Vivek D

Member
I have different snapshots of financial data e.g. Target/Plan, Prior Year Results, Current Forecast, Previous Forecast etc. There's huge amount of base data involved (1-2 million records).

I am preparing a dashboard as part of which I want to provide user the option to select what view they want to see and variance against what snapshot e.g. Current Forecast with variance against Prior Year results OR Target with variance against Prior Year Results OR Current Forecast with variance against Target ( as shown below) etc.

PowerPivot.png

I found a technique here that shows how one can achieve that but it's applicable only if input is a numeric value. In my case, the input is text that I want to use in a CALCULATE formula.
e.g. Something like
Rev:=CALCULATE(SUM([Revenue]),Financials[Type]=MainView[Main View])

How can I use the technique with text values or is there another way to achieve what I want?

Note: I do want to ideally achieve the result using pivot tables only instead of formulas as I need the ability to drill (expand/collapse) from BU to Client to Project or any other dimension.
 

Attachments

  • Power Pivot.xlsx
    218.4 KB · Views: 8
Last edited:
Adding a distinct did the trick...
CALCULATE([Rev],Financials[Type]=DISTINCT(MainView[Main View]))

Still open to any other suggestions though as the above seems more like a hack than a good solution.
 
Back
Top