difference between columns in pivot table

[Ffitzpatrick47]

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

[v-huizhn-msft]

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

[Ffitzpatrick47]

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. 

[Ashish_Mathur]

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

Amount=SUM(Data[Amt])

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

Hope this helps.

[Ffitzpatrick47]

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

[v-huizhn-msft]

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

[Ffitzpatrick47]

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

[v-huizhn-msft]

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