Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Helper II
Helper II

difference between columns in pivot table

Time was, in a power pivot we could make an additional item that was the difference between two other columns in a pivot table.

So, if I had a pivot table with budget and actual, I can make a difference item too, and then could all pivot around some sum.  Is there a way to do something similar to this in power pivot without making three measures, two for budget and actual, and then one to calculate the difference?  I just can't imagine making n^2 difference calculations where n = number of columns I have where every other pivot tool just lets you make calculations on the aggregated table based on the position of the values on the table so that it can be used dynamically


Hi @Ffitzpatrick47,

>> Is there a way to do something similar to this in power pivot without making three measures, two for budget and actual, and then one to calculate the difference? 

If my understanding is right, you want to know how to achieve the similar result in Power BI, right?

If it is, what your resource table look like? Could you please share your sample table, and post a pivot table screenshot, so that we can reproduce your scenario and the similar result in Power BI desktop. 

Best Regards,

So in other apps, you can refer to columns dynamically and have one column function refer to column{1} - column{0} and work right on the aggregate data. Real pivot tables didn't have this feature, but it did allow you to make items whic approximated it, but still from an atomic level.


And here it is


Power pivot is measures based, so you'd have to do [x2015]=calculate(sum([amt]),[year]=2015), [x2014]=calculate(sum([amt]),[year]=2014) and [dif] = [2015]-[2014].  But if there are 2020->2000 years, there are 380 different dif functions and 20 different calculations just to handle these amounts.  There must be some easier way to do this.  Maybe artificial pivot tables with cubevalues or something. 

Hi @Ffitzpatrick47,


Ensure the following:

  1. There is a calendar table
  2. There is a relationship from the Date column of your Data Table to the Date column of your Data Table
  3. Extract Year using =YEAR(Calendar[Date]) in the Calendar table
  4. Drag Year from the calendar table in yoru visual

Use the following calculated field formulas to get yearwise/fruit wise values





Hope this helps.

Ashish Mathur

I just did the years as an example.  In reality, none of the date logic helps me because Microsoft must work on a conventional calendar, so they've never been able to accomodate modern calendar innovations like a 13 month, or 32 day or 445.  Those columns can also be different regions like north east south west, or different teams of people like lakers, celtics.  The possibilities are many.  My sense is, no.  Microsoft doesn't have a concept that deals with data in the aggregate.  They want you to address it from the transaction up, which makes it twice as slow because if you want to do sales 2015 vs 2014, you'd have to pull it twice.  They probably have some patented concept to cache it magically rather than trust the user to just use the aggregated data explicitly.  Alas... Well, it is free...

Hi @Ffitzpatrick47,

You can add a field in value level of visual, then use the "Quick measure" to display expected result. More details, please see:


Best Regards,

Fantastic. Show me what it does in excel 2013 because billion dollar corporations won't let you put up corporate data on the web or install power bi if its not official like excel is. You can verify this yourself if your working at Microsoft. Try to use libre office and pipe into a downloaded version of post gre or even better, amazons free postgre service

Hi @Ffitzpatrick47,

Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Best Regards,

Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors