Different number formatting in same column

[Anonymous]

Hi PowerBI community,

I know that a column is designed to hold only one data type (and formatting) but wanted to share a use case with you and see if you had suggestions on how to tackle it.

I have a table following the below concept:

Metric	Year	Value
Profit	2018	$20.5
Profit	2019	$30.6
Profit	2020	40%
Efficiency	2019	92.68%
Efficiency	2020	95.78%
Volume	2019	50000
Volume	2020	60000

What I'd like to achieve is having a filter on the metric column and then display in a time series graph the relevant values formatted as per the data above (note some are $, some are %, with 2 or 1 decimal points). The formatting will not change by category (ie all efficiency values will be ##.##%).

I've tried two things but to no avail:

• a format function returning an error as I was mixing data types in the same column
• creating as many columns as there are metrics and displaying them all (with blank when non relevant). This worked well but with over 20 metrics, the graph became unfortunately unusable due to the blank spaces.

Do any of you have a suggestion by any chance?

Thanks in advance.

OF

[shreyanka_s2011]

How to do conditional formatting for different number formatting in same column?

Example : A column has percetage value as well as number values, I want to color entire column with the condition if a value or percentage > 0 the "Green" else "Red"

[v-shex-msft]

HI @Anonymous,

You can use switch function with format functions to achieve dynamic number format on your visuals.
Notice: these value are converted to text type after format function applied. Current power bi does not support displaying multiple numeric formats in the same fields. You can try to submit an idea for this requirement.

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , Dynamic formatting is allowed. You have format the number in the measure. But this will change data type text.

Refer: https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings

[harshnathani]

Hi @Anonymous ,

You can create a meaure

Measure =
VAR _selval =
    SELECTEDVALUE ( 'Table'[Metric] )
RETURN
    SWITCH (
        TRUE (),
        _selval = "Profit", FORMAT (
            SUM ( 'Table'[Values] ),
            "Currency"
        ),
        _selval = "Efficiency", FORMAT (
            SUM ( 'Table'[Values] ),
            "00%"
        ),
        _selval = "Profit", SUM ( 'Table'[Values] )
    )

https://community.powerbi.com/t5/Desktop/Different-format-in-Target-column/m-p/818394

Regards,

Harsh Nathani

[Anonymous]

Hi Harsh,

First off, thanks very much as I think I'm close to the solution with this. A couple of questions:

. what can I do in the measure for "everything else", ie if I want to control the formatting of 3 specific metrics but I'm ok for the rest to be displayed as they are and don't require further manipulating.

. the measure shows data correctly in a card but displays nothing on a bar chart, can you thin of any reason why?

. finally, what is the point of the last part of your formula?

Thanks a lot.

OF