I believe that you'll need to re-structure your data a bit to get this.
So in the data source window, control and select sales and stock, then right click to pivot:
You should then get two new columns Pivot Field Names and Pivot Field Values, feel free to rename them. But essentially pivot field names now contains the text values of Sales or Stock, the the pivot values contain the values.
Now we can use some LODs to work out the %. First I created two calculations, one for BP and one of LY:
Then I created two more calculated fields to work out the %:
Then I brought them into the view:
With this method, the non period options (e.g. actuals, business plan) will still show all 3 metrics.
So what you can do is create two sheets and combine them into one view:
Thanks and please see attached.
Calculation Help_v10.2.twbx 127.9 KB
The approach was lot of help . Thanks for giving me an insight.
In the below Sales Vs BP = Sales/BP *100 and number formatting %
but Stocks= Sales-BP and number formatting is standard with 2 decimal points
How can we achieve this.
You can right click on the field in the data pane and change the default properties for the number format.
Or you can right click on the pill in measure values bucket and change the number format there.