Forum Discussion

jvahlund's avatar
jvahlund
Copper Contributor
Oct 04, 2021

Excel pivot calculated field

Hello, I have a problem which I dont know how to solve.

 

In my pivot, I have added a column named "Weighted Total %" based on the column next to it called "Weighted Totals". This percentage represents the percentage of a specific value (389060)

 

What I would like to do is to add a calculated field where each row calculate this percentage out of the value 389060.

 

So for example, if the column 'Weighted Total %' equals 0,04% it should calculate as 0,04*389060 etc.

Anyone have any idea if this is possible?

 

 

excel

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 04, 2021

    jvahlund 

    That could be done with data model measure (if only you are not on Mac). Creating PivotTable add data to data model, for such sample

    add DAX measure as

    BB:=VAR grandTotal=CALCULATE( SUM(Table1[B]), ALL(Table1) ) RETURN DIVIDE(SUM(Table1[B]), grandTotal, 0)*389060

    and use it in PivotTable.

    • jvahlund's avatar
      jvahlund
      Copper Contributor
      Oct 05, 2021

      SergeiBaklan 

       

      Thank you so much for your reply. I actually came around this issue with another solution (in my SQL statement.) But I really appreciate your time and effort in replying to this.

       

      I'll have that in mind until the next time 🙂 

       

      Cheers!

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Oct 05, 2021

        jvahlund , you are welcome, thank you for the feedback.

        In general creating PivotTable it's always better to work with data model, you have much more power compare to PivotCache PivotTable.

Resources