Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Get percentage based on three columns/ three different values

Good Afternoon,

 

I am trying to divide Rating/Question (In this case, PRSQ5000) by the total count of 4006.

Manually I was been able to, but since I need to use an slicer the percentage need to update accordingly.

I tried converting the 84 to Show as Value: Percentage of Grand Total and the result I get is always 100%. Below picture for reference of the fields and applied filters:

I did the following (Sum of Rating of 1 and question: PRSQ5000 then divided by the grand total of 4009) but it did not work as expected even tho I got the expected percentage:

I would greatly apprecite if someone with knowledge could contact me for more details.

 

 

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anonymous 

    Here are the steps you can refer to :

    (1)This is my test data which is the same as yours:

    (2)We can create two measures :

    Response = IF( HASONEVALUE('Sheet3'[RATING]), COUNT('Sheet3'[QUESTION|]) , FORMAT( DIVIDE( SUM('Sheet3'[RATING]) ,  COUNT('Sheet3'[RATING])),"0.00")  )
    % = DIVIDE([Response], CALCULATE( COUNT('Sheet3'[KPI_SURVEY_ID|]), ALLSELECTED('Sheet3'[RATING]) ))

    (3)Then we can put the measure and the filed we need in the Matrix visual and we will meet your need:

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

     

     

     

5 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi , Anonymous 

    According to your description,  you can put your field in a Matrix visual and then write a measure to implement it according to your needs.

    For your question,I don't have a clear idea which field your numerator and denominator come from, and how [Rating] is grouped,can you provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Good morning, 

       

      Thanks! I will try the matrix. Providing additional information below;

      File download: https://anonfiles.com/udJ5jfA3y9/KPI_s_Draft_pbix

       

      Numerator: column.QUESTION and column.RATING

      Denominator: column.KPI_SURVEY_ID (This is the total count to be divided by)

      The outputs should be getting the percentage of each rating under the specific question. The image I uploaded can be used as reference, I will include on this reply a whole screenshot of the full page.

      MY SCREENSHOT:

      The percentages I got are put manually, this cause the slicer to not work.

      WHAT I AM TRYING TO REPLICATE WITH UPDATED DATA:

       

      SAMPLE DATA;

      KPI_SURVEY_ID|QUESTION|RATING
      22PRSQ50015
      23PRSQ50025
      24PRSQ5003 
      25PRSQ50005
      26PRSQ50011
      27PRSQ50021
      28PRSQ5003 
      29PRSQ50001
      30PRSQ50012
      31PRSQ50023
      32PRSQ5003 
      33PRSQ50005
      34PRSQ50011
      35PRSQ50025
      36PRSQ5003 
      37PRSQ50002
      38PRSQ50014
      39PRSQ50024
      40PRSQ5003 
      41PRSQ50005
      42PRSQ50011
      43PRSQ50021
      44PRSQ5003 
      45PRSQ50003
      46PRSQ50014
      47PRSQ50024
      48PRSQ5003 
      49PRSQ5003 
      50PRSQ50003
      51PRSQ50014
      52PRSQ50025
      53PRSQ5003 
      54PRSQ50004
      55PRSQ50011
      56PRSQ50025
      57PRSQ5003 
      58PRSQ50001
      59PRSQ50012
      60PRSQ50022
      61PRSQ50005
      62PRSQ50011
      63PRSQ50025
      64PRSQ5003 
      65PRSQ50005
      66PRSQ50011
      67PRSQ50021
      68PRSQ5003 
      69PRSQ50004
      70PRSQ50011
      71PRSQ50025
      72PRSQ5003 
      73PRSQ50005
      74PRSQ50015
      75PRSQ50025
      76PRSQ5003 
      77PRSQ50004
      78PRSQ50004
      79PRSQ50011
      80PRSQ50011
      81PRSQ50021
      82PRSQ50021
      83PRSQ5003 
      84PRSQ5003 
      85PRSQ50004
      86PRSQ50011
      87PRSQ50021
      88PRSQ5003 
      89PRSQ50004
      90PRSQ50011
      91PRSQ50021
      92PRSQ5003 
      93PRSQ50005
      94PRSQ50011
      95PRSQ50025
      96PRSQ5003 
      97PRSQ50003
      98PRSQ50014
      99PRSQ50024
      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi, Anonymous 

        The .pbix you provide i cannot open it , and i will research your problem through the introduction you provide.Can you share the .pbix to us just with oneDrive link ? And can you provide a sample output table with your sample data you provided.

         

        Best Regards,

        Aniya Zhang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly