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.

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

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
Last edited: