March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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:
Do any of you have a suggestion by any chance?
Thanks in advance.
OF
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"
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
@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
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
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
User | Count |
---|---|
117 | |
77 | |
58 | |
52 | |
46 |
User | Count |
---|---|
171 | |
117 | |
63 | |
57 | |
51 |