Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 BI I am able to calculate the % via a measure but how can I make sure it displays in the 1:50 format?
With kind regards,
Mike
Solved! Go to Solution.
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
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)?
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
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
very useful,thanks
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
THis measure will work for you below.
ROUNDUP(DIVIDE(SUM('Table'[Total Leads]),SUM('Table'[Count aprroved])),0) & ":1"
And here is the output
Awesome that workes! Thanks!
Perfect, glad it solved your issue.
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!
User | Count |
---|---|
127 | |
108 | |
100 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |