Forum Discussion

Ffitzpatrick47's avatar
Ffitzpatrick47
Helper II
8 years ago

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

Need Help

7 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Ffitzpatrick47
      Helper II
      8 years ago

      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's avatar
        Ashish_Mathur
        Super User
        8 years ago

        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.