Forum Discussion

Oraclein's avatar
Oraclein
Copper Contributor
Mar 19, 2020

Powerpivot calculations

Hi  I'm trying to add variance rows to a pivot table created in power pivot. The pivot is pulling actuals and budget as scenarios in rows , and I want to add a variance block of rows underneath.  ...
BI & Data Analysis
ChrisMendoza's avatar
ChrisMendoza
Iron Contributor
Mar 19, 2020

Oraclein -

I think in all of the DAX patterns I have seen involving Budget vs. Actual's the values have been in separate tables. In your example, I only see a normal Pivot Table with a your months cross tabulated and nothing in the Data Model.

 

I think you will find it difficult to visually represent Net Sales, COGS, Gross Margin in your desired row context as those calculations are created in measures.

 

Possibly the simple example I have created will work for your needs  but if not, take a look at https://www.daxpatterns.com/budget-patterns/ and https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

  • Oraclein's avatar
    Oraclein
    Copper Contributor
    Mar 20, 2020

    ChrisMendoza Thanks Chris - apologies I didn't add the powerpivot to my example as there is so much data in the original table and I was rushing a simplified model.

    I'm working through your answer and am hopeful I'll be able to resolve

Resources