Different number formats

[MagubaneSSP]

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!

[Greg_Deckler]

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

[MagubaneSSP]

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!

[Greg_Deckler · 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")
    )

[MagubaneSSP]

Hi @Greg_Deckler ,

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