I have combined two data sets - one had Sales Forecasts and the other had Sales Actuals. I added an extra column to the data to denote if the value was an Actual or a Forecast.
I have created a Pivot table and put the Actual/Forecast field in the Columns box so I have Month -> Actual and Forecast.
The problem is the total for each month is adding the Actual to the Forecast, instead I would like to change this to instead show the difference between the two.
Pivot Variance.PNG
Is this possible?
It's possible, if you use the data model, and create a measure (using DAX).
Attach a sample workbook showing your data structure and required output, then I can demonstrate an appropriate measure.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
I have created a dummy Workbook to show you, but the attachment button isn't working for me in Chrome or Edge.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Many thanks Olly. See attachment.
Load your source data table to the data model - I've done it via Power Query, and named the table "Source Data".
Now you have at least two options to achieve your requirement.
Option1
Add three measures to your model / pivot, and remove the Actual/FC field from the pivot table;
Please Login or Register to view this content.Please Login or Register to view this content.Please Login or Register to view this content.
Option 2
Add one more complex measure:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks