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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

7 REPLIES 7
Microsoft Employee

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,
Angelila

Helper II

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.

Super User

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

Amount=SUM(Data[Amt])

Diff=[Amount]-CALCULATE([Amount],PREVIOUSYEAR(Calendar[Date])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper II

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

Microsoft Employee

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: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-quick-measures/

Best Regards,
Angelia

Helper II

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

Microsoft Employee

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,
Angelia

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors