Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Displaying % as Ratio

Hi All,    I am trying to make a % visible in a different format.  E.g. Instead of 1.81% I want to show 1:50.   In Excel I use the following formula: "="1" & ":" & ROUND(($B$1/C1),0)" In Power ...
Need Help
Tips and Tricks
Tutorial Requests
  • GilbertQ's avatar
    GilbertQ
    9 years ago

    Hi Anonymous

     

    No worries, please see below the proposed solution.


    Here is the measure that I created

    Column E Result = "1:" & ROUND(CALCULATE(SUM('Table1'[A])/SUM('Table1'[B])),0)

    And this is what the result looks like

     

GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
9 years ago

Hi Anonymous

 

Could you provide a sample of data, one where it would calculate the % and one where it would be the 1:xx (Or even if the values are the same, what the sample data is)?

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi there!

 

Thanks for your response please see below:

In the Excel formula its ROUND(B/A,0)

 

A       B      C      D     E
80000   2000   0.025  40    1:40
3000    20     0.006  150   1:150
400     5      0.0125 80    1:80

 

I get to calculate column C as well as the correct result for behind the : now how do I format it so that it looks like 1:##?

Ratio = FORMAT(ROUND(divide(sum(B),sum(A),0),"1:")

 

Bests,

Mike

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    Hi Anonymous

     

    No worries, please see below the proposed solution.


    Here is the measure that I created

    Column E Result = "1:" & ROUND(CALCULATE(SUM('Table1'[A])/SUM('Table1'[B])),0)

    And this is what the result looks like

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Awesome that workes! Thanks!

      • GilbertQ's avatar
        GilbertQ
        Icon for Super User rankSuper User
        9 years ago

        Perfect, glad it solved your issue.

    • Ashish_kumar12's avatar
      Ashish_kumar12
      Icon for Helper I rankHelper I
      6 years ago

      Hi  GilbertQ  , Can you please assist me in my scenario.

       

      I have created a ratio and am trying to place it in MATRIX visual as have to display data in following manner -

       

      Consultant

      Total Leads submitted

      Count of Approved Leads but no orders

      Leads not approved

      Count of Approved Leads and orders Placed

      Total Leads : Approved Leads and orders Placed

      A

      47

      2

      35

      10

      =Round(47/10,0)   &":1"

      = 5 : 1

      B

      20

      5

      5

      10

      2 : 1

       

      When I am trying to do the same in matrix visual it is not happening. When I place ratio without the columns with count then it is working - image below is as per data of Consultant A above

       

       

       

      Also, another query is, since it is creating ratio in text format, I wish to sort the ratios with lowest ratio on top to highest ratio.

      Example: instead of 2:1, 37:1 then 5:1 wish to display information as 37:1, 5:1 then 2:1

       

      Please let me know.

       

      Thanks.

      Regards,

      Ashish

       

       

       

       

       

       

       

       

       

      • GilbertQ's avatar
        GilbertQ
        Icon for Super User rankSuper User
        6 years ago

        Hi Ashish_kumar12 

         

        THis measure will work for you below.

         

        ROUNDUP(DIVIDE(SUM('Table'[Total Leads]),SUM('Table'[Count aprroved])),0) & ":1"

         

        And here is the output

         

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      very useful,thanks