Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate percentage from same column

Hello,

 

I was wondering if there is a way to calculate the percentage of a value in a column based on a different value in the same column. To do this I'd like to use a different column as an index.

For instance, on the attached picture, I would like to calculate the percentage of Total Labor on Total Cost.

 

Thanks for your time.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hello Anonymous

     

    Try this please:

     

    Percentage= DIVIDE(CALCULATE(SUM(Table[Month Budget]);FILTER(Table;Table[Category]="Total Labor"));CALCULATE(SUM(Table[Month Budget]);FILTER(Table;Table[Category]="Total Cost")))

     

    I hope this works.

     

    PS, in "Table", you've to put the name of the table.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hello Anonymous

     

    Try this please:

     

    Percentage= DIVIDE(CALCULATE(SUM(Table[Month Budget]);FILTER(Table;Table[Category]="Total Labor"));CALCULATE(SUM(Table[Month Budget]);FILTER(Table;Table[Category]="Total Cost")))

     

    I hope this works.

     

    PS, in "Table", you've to put the name of the table.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Sorry, I didn't fully explain my situation. I have dozens of clients and every client has those 5 rows with a client ID. Your solution, while elegant, it will only work if there was only one set if clients.

       

      I ended up splitting the table into 5 smaller tables each containing one category and then doing calculations that way. I know it's not clean.

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous

         

        It seems you may use "Append Queries" in query editor to combine the tables. Then you may use the measure as HarrisonCbe mentioned. If it is not your case, please share sample data for your scenario so that we could help further on it.

         

        Regards,

        Cherie