Forum Discussion

IanMSg's avatar
IanMSg
Copper Contributor
Feb 04, 2020

Prorating Values in a Pivot Table

I have 2,000 transactions that I have hand classified from a few points of view.

Some transactions belong to 2 Classifications - in the example below both A and B

What I am looking to do is for the Pivot Table not to treat A,B as a new classification but to prorate the value to A and to B ie in this case the 20 as 10 to A and 10 to B in both cases  (Description 3 & 4)

I could duplicate each Tx that belongs to both Classifications and half the value - but its already 2k big and this would blow it up.

Any ideas on how to make pivot tables + Formula work this for me?

 

BI & Data Analysis
excel
Formulas and Functions

3 Replies

    • IanMSg's avatar
      IanMSg
      Copper Contributor
      Feb 04, 2020

      PascalKTeam 

       

      Thanks for the note - sorry no, am not familiar with Power Query - can you point me at some info please

      • PascalKTeam's avatar
        PascalKTeam
        Iron Contributor
        Feb 04, 2020
        Basically I'm using Excel Power Query to access your file, make the desired manipulation in Power Query and then show the result in my file. As you can see, my file has split the rows with "A,B" into two rows and has split the value in half.

        If you open my file, go to "Data" - "Get Data" - "data source setting"
        Then select "change source" and navigate to the source file which you have saved on your PC and hit "OK"
        After that click on "Data" - "Refresh all" and you should see the desired result.

        Let me know if this was not clear

Resources