# PowerPivot: divide one column by another



## lofty147 (Feb 14, 2017)

How do I show percentages in a powerpivot pivot table so that at parent levels of rows, it shows the correct percentages rather than summing up 



In a powerpivot pivottable the 'calculated field' option is greyed out - so I cannot use this to divide column A by column B.
I could use a DAX formula in the powerpivot window to divide column A by B. The problem is, my data is on a very low level, so when viewing the pivot table in excel - it sums up all the percentages at a higher level (parent level).
E.g. local level shows accurate 5% profit, but the region sums up all the percentages to show 80%, and the country sums up all regions to show 400%
I don't think 'measure' will work either.

Sorry if I'm not being clear, can provide further info if needed. Does anybody have a solution?


----------



## MarcelBeug (Feb 14, 2017)

Use the Query Editor. Choose Add Column, select the columns in the correct order, choose "Standard" - "Divide" and you get a new column in which the values in the first selected column will be divided by the values in te second selected column.


----------



## lofty147 (Feb 14, 2017)

thanks for the reply. Do you mean to use the power query tab? 

Would you be able to provide further steps as I have not used this before....


----------



## lofty147 (Feb 14, 2017)

All my data is in access, as I am using that as the database to read into powerpivot - so i cannot use powerquery over powerpivot.

The solution would be to use the 'calculated field' option in the pivottable, but you cannot use this in a powerpivot table


----------



## MarcelBeug (Feb 14, 2017)

There must be some query that imports the data from Access. So you are not using Power Query over Power Pivot, but Power Pivot over Power Query.


----------



## MarcelBeug (Feb 14, 2017)

Apologies. It looks like you can import Access data directly into your datamodel, bypassing Power Query. I don't know if you can still redirect the connection via Power Query.


----------



## MarcelBeug (Feb 14, 2017)

Maybe this link can help you.


----------

