Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Power Bi Experts,
I am struggling with displaying numbers in different formats on a power bi visualize or matrix. Simply put, I want to be able to show percentages and whole numbers in one visual. My excel data looks like this:
Country | Indicator | Target | Actual | % Achievement |
A | No. of customers | 500 | 335 | 67% |
B | No. of customers | 300 | 400 | 133% |
A | Profit growth | 40% | 25% | 63% |
B | Profit growth | 10% | 40% | 400% |
When I load this data to power bi, all the percentage numbers change to decimal numbers, e.g. 40% becomes 0.4. I have tried the SWITCH function and formatting number strings under modelling, but the formatting is never right. I suppose the tricky part is that countries have the same indicators, but different targets - and also we have whole numbers and percentages in one column? Any ideas on how I could address this?
One other thing I want to show on my visuals is total targets and total actuals by indicator, e.g. Customer Target (800) and Customer Actual (735) - however, my visual always gives me the max value (500 Customers), and then I have to put in a country slicer to filter between the 2 regions. This is inefficient and I want my visual to show our overall achievement against each indicator. In this way I can develop a tooltip so that when you hover on my visual you can see how each country performed on each of the indicators. Any ideas on how I could address this, factoring the number formatting issue?
Thank you Power Bi Experts!
Solved! Go to Solution.
@MagubaneSSP Here is a percent, whole number and decimal format:
Measure 2 =
VAR __Value = SUM('Table'[Column2])
RETURN
SWITCH(MAX('Table'[Column1]),
"one",FORMAT(__Value,"Percent"),
"two",FORMAT(__Value,"##"),
"three",FORMAT(__Value,"00.00")
)
@MagubaneSSP Yeah, the way you have it setup now you would have to use FORMAT strings to format it depending upon the situation. For the rest of it, hard to know for sure. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Thank you for your considered response, could you please share the format string to show different formats in one visual?
I have been able to solve the other issue using the SUM function - but the visual is still not correct becuase of the different number formats.
Thank you!
@MagubaneSSP Here is a percent, whole number and decimal format:
Measure 2 =
VAR __Value = SUM('Table'[Column2])
RETURN
SWITCH(MAX('Table'[Column1]),
"one",FORMAT(__Value,"Percent"),
"two",FORMAT(__Value,"##"),
"three",FORMAT(__Value,"00.00")
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |