Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculating Percentages

So I have this calculated table (I derived the table using a CALCULATE()), that looks like this.   Dept Location No of Ppl Sales USA 40 Sales Denmark 20 Sales Sweden 40 Sales...
Need Help
Show and Tell
  • Sean's avatar
    Sean
    9 years ago

    Anonymous

    Okay if you really want COLUMNS here are those formulas...

     

    Total People COLUMN = CALCULATE ( SUM ( 'Table'[No of Ppl] ), ALLEXCEPT('Table', 'Table'[Dept]) )

Percentage COLUMN = DIVIDE ( 'Table'[No of Ppl], 'Table'[Total People COLUMN], 0 )

    But in case you want MEASURES

     

    Total People MEASURE = CALCULATE ( SUM ( 'Table'[No of Ppl] ), ALLEXCEPT('Table', 'Table'[Dept] ) )

Percentage MEASURE = DIVIDE ( SUM('Table'[No of Ppl]), [Total People MEASURE], 0)

    Hope this helps!

    Good Luck! :smileyhappy:

     

Sean's avatar
Sean
Community Champion
9 years ago

Anonymous

Okay if you really want COLUMNS here are those formulas...

 

Total People COLUMN = CALCULATE ( SUM ( 'Table'[No of Ppl] ), ALLEXCEPT('Table', 'Table'[Dept]) )

Percentage COLUMN = DIVIDE ( 'Table'[No of Ppl], 'Table'[Total People COLUMN], 0 )

But in case you want MEASURES

 

Total People MEASURE = CALCULATE ( SUM ( 'Table'[No of Ppl] ), ALLEXCEPT('Table', 'Table'[Dept] ) )

Percentage MEASURE = DIVIDE ( SUM('Table'[No of Ppl]), [Total People MEASURE], 0)

Hope this helps!

Good Luck! :smileyhappy:

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hey Sean,

 

I'm looking only for Columns. But the SUM() formula to calculate no of people doesn't seem to help if I have more than 1 department.  I'm sending you a sample below.

 

DeptLocationNo of PplTotal PplPercentage
SalesUSA402500.16
SalesDenmark202500.08
SalesSweden402500.16
SalesAustralia302500.12
SalesNorway802500.32
SalesIndia102500.04
SalesChina302500.12
HRUSA201370.15
HRDenmark101370.07
HRSweden501370.36
HRAustralia201370.15
HRNorway251370.18
HRIndia101370.07
HRChina21370.01
  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    Anonymous

    Formulas revised above! :smileyhappy:

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      That works! Thanks a ton!!! :)

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Shirley,

     

    I have the same issue, as you did. Just with "Used budget" and "Budget", where I want to find the "Percentage used" for each department. 

     

    I'tried the formules under Colums as mentioned above, but it doesn't work. Can you show mewhat you wrote in the formula bar to get your percentages?

     

    Regards Jonas