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

     

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
Super 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
      Super User
      9 years ago

      Perfect, glad it solved your issue.

      • choiyn98's avatar
        choiyn98
        Frequent Visitor
        3 years ago

        Could you help me with my scenerio? 

        I created a Matrix in PBI and I want to calucate the ratio between 2023 with 2022 and another column for 2023 with 2021.

         

         

         

        The result I want is to show % change between 2023 with 2022 and another column for 2023 with 2021.

         

        % Change with last year

        -8%

        -13%

        5%

        -48%

        24%

        -25%

        14%

        56%

        -11%

        -5%

        % Change with 2 years

        -25%

        -11%

        88%

        -36%

        20%

        -15%

        -31%

        21%

        97%

        4%

         

         

         

        Thanks in advance!

  • Ashish_kumar12's avatar
    Ashish_kumar12
    Helper 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
      Super 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