Forum Discussion
difference between columns in pivot table
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
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:
- There is a calendar table
- There is a relationship from the Date column of your Data Table to the Date column of your Data Table
- Extract Year using =YEAR(Calendar[Date]) in the Calendar table
- 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.
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...
