locked
How do I create percentage calculated fields? RRS feed

  • Question

  • I have the following table,here are many more values in the rows but this is just an isolated example.

    What I want to do is use a calculated field to add an additional column next to each existing column that represents each value as a percentage of the row total. Now I know I can simulate this by right clicking the PowerPivot table and using "Show Values as..." which gives me the right information e.g. below:

    This is not however the right layout, I want these percentages to be next to the actual values. The reason for this is so that I can sort by both percentage and value. If you Show Value As... then you can only sort by the underlying value.

    I'm sure this should be a super simple DAX formula but I just can't work it out. I can get  a formula working that will generate the percentage for each dimension i.e. one for Accepted, one for Pending and one for Rejected, however this will result in far too many value columns.

    Can anyone please help?

    Thanks,

    James

    Thursday, August 22, 2013 11:30 AM

Answers

  • Hello James,

    It is pretty simple, you just need a calculated column with a simple formula.

    - Open PowerPivot
    - Add a new column "Total" = Accepted + Pending + Rejected; it will make it easier.
    - Then add a new column "Accepted Per" = [Accepted] / [Total] and format the column as %
    - Dito for Pending & Rejected

    That's all, you can add the columns in any order you like. This even works when you aggregate the values; like below "Test 3":


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, August 22, 2013 4:18 PM