Forum Discussion

veshva40's avatar
veshva40
Regular Visitor
9 months ago
Solved

Help with Divide Function (total percentage)

Hi everyone, I’m having some trouble calculating percentages in Power BI. As you can see in the image below, I want it to show the percentage of completion for each batch separately. But right now, the formula I’m using is calculating the overall percentage for all three batches instead of doing it per batch.

Any ideas on how to fix this?

 

Current outcome

Expected outcome

 

Thank you

Need Help
  • MasonMA's avatar
    MasonMA
    9 months ago

    It looks like a syntax error in your SUM(). It's missing a bracket after 'Table1'[TOTAL]. 
    Try this instead. 

    Total% =
    DIVIDE(
    'Table1'[TOTAL],
    CALCULATE(
    SUM('Table1'[TOTAL]),
    ALLEXCEPT('Table1', 'Table1'[Batch])
    )
    )

4 Replies

  • MasonMA's avatar
    MasonMA
    Super User
    9 months ago

    Hi, 

     

    I assume your current Measure probably looks like 
    Total % = DIVIDE([Total Person], CALCULATE(SUM('Table'[Total Person]), ALL('Table'))) ?

    If so, it removes all filters, including the Batch filter so the denominator is the grand total of all batches.

    Try updating your denominator to 
    CALCULATE(
    SUM('Table'[Total Person]),
    ALLEXCEPT('Table', 'Table'[Batch])
    )

    so that it removes all filters except the batch.

  • veshva40's avatar
    veshva40
    Regular Visitor
    9 months ago

    Thank you for your advice. Based on your reply, I tried below computation but it throws me error 'too many arguments were passed to the SUM function. The maximum argument count for this function is 1.

     

    Total% = DIVIDE('Table1'[TOTAL],CALCULATE(SUM(Table1[TOTAL],ALLEXCEPT(Table1,Table1[Batch]))))
    • MasonMA's avatar
      MasonMA
      Super User
      9 months ago

      It looks like a syntax error in your SUM(). It's missing a bracket after 'Table1'[TOTAL]. 
      Try this instead. 

      Total% =
      DIVIDE(
      'Table1'[TOTAL],
      CALCULATE(
      SUM('Table1'[TOTAL]),
      ALLEXCEPT('Table1', 'Table1'[Batch])
      )
      )

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    9 months ago

    Hi veshva40 ,

    try creating below measure:

    percentage = DIVIDE(
     SUM(Demo[Total Person]),
     CALCULATE(SUM('Demo'[Total Person]),ALLEXCEPT('Demo','Demo'[Batch]))
)

     

    Please Give Kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    Praful