Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculation into percentage %

Hello PowerBI Community 😁

 

I'm new with PowerBI, query, & Stuffs. & I need help with something.

 

So i want to change all the data in table below into percent % like this,

For example - safety column, table below:

Max Value in safety (20)

Logic / Formula: Example if a person have 1 task and in safety column he got "13" it means (13/20 * 100%) = 65%

if a person got "20", (20/20 * 100%) = 100%

 

After i calculate it into percentage %, i want to average all the data ini Safety Column except "None" (in the table below), then show it as AVG percentage % (in the table above).

 

How can i do this in PowerBI? i've tried using ifs, divide calculation, and nothing works for me 😵

 

Any help will be appreciated 🙏

Need Help
Tutorial Requests

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , I case you need a new table non should be null

    You can repeat safety formula for other columns, count and sum you may have to change

     

    New Table = summarize(Table, table[employee], "Tasks", countrows(Table]),"safety", divide(sum(Table[safety]),count(rows(Table])*20)))

     

    This can be use as a measure too

    divide(sum(Table[safety]),count(rows(Table])*20))

     

    if this do not work

    Can you share sample data and sample output in table format?

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You could create measures for each column you want.

    safety =
CALCULATE (
    AVERAGE ( 'Table'[safety] ),
    ALLEXCEPT ( 'Table', 'Table'[Employee] )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-eachen-msft 

      it's still doen't work 


      *In case you need it:
      This is the safety colum, before this i've to define all the ratings into numbers like:
      Rating 1: 6.5
      Rating 2: 13
      Rating 3: 20
      and if the field is empty, it should be "None" not 0 (this one is my requirement)

      And i'm adding a new measure that you provide

      I will provide some sample of data here,

      Safety (20)Safety (%) - Expected result
      20(20/20 * 100%) = 100%
      20(20/20 * 100%) = 100%
      NoneNone
      NoneNone
      1365%

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

         

         

        Create a measure.

         

        Safety% =
IF (
    MAX ( 'Table'[Safety(20)] ) = "none",
    0,
    DIVIDE (
        MAX ( 'Table'[Safety(20)] ),
        20
    )
)

         

        or Create a  Calculated Column

         

        Safety% =
IF (
    'Table'[Safety(20)] = "none",
    0,
    DIVIDE (
        'Table'[Safety(20)],
        20
    )
)

         

        Regards,
        Harsh Nathani
        Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)