PowerPivot: divide one column by another

[lofty147]

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]

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]

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]

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]

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]

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]

Maybe this link can help you.