cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MagubaneSSP
Frequent Visitor

Different number formats

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:

 

CountryIndicatorTargetActual% Achievement
ANo. of customers50033567%
BNo. of customers300400133%
AProfit growth40%25%63%
BProfit growth10%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!

1 ACCEPTED 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")
    )

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Hi @Greg_Deckler 

 

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")
    )

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Hi @Greg_Deckler ,

 

Thank you so much - this works perfectly, for now!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.