Forum Discussion
Formatting values separately in unpivot value column
Hi All,
I have below data-
| Category | Location | Parameter1 | Parameter2 | Parameter3 | Parameter4 | Parameter5 | Parameter6 |
| C1 | L1 | 25 | 5% | 54 | 2% | 24 | 19% |
| C2 | L2 | 48 | 8% | 33 | 3% | 99 | 8% |
| C3 | L3 | 91 | 3% | 13 | 1% | 63 | 15% |
| C4 | L4 | 77 | 5% | 32 | 2% | 19 | 20% |
| C5 | L5 | 37 | 3% | 72 | 1% | 63 | 34% |
| C6 | L6 | 57 | 5% | 35 | 1% | 28 | 7% |
| C7 | L7 | 19 | 3% | 48 | 1% | 92 | 15% |
| C8 | L8 | 71 | 8% | 96 | 2% | 33 | 10% |
| C9 | L9 | 96 | 4% | 23 | 1% | 29 | 6% |
| C10 | L10 | 36 | 4% | 69 | 1% | 46 | 6% |
As I want to create a column chart along with a parameter selection filter, I unpivot the data as below.
I have generated the column visual as below. The problem is since I unpivot the columns, the absolute value columns (Parameter1, Parameter3, Parameter5) & % columns (Parameter2, Parameter4, Parameter6) got into a single value column removing the % sign from % value fields. Now when I select a parameter with % values, the chart shows % values in decimals. How can I bring the % format for the % values in this case?
Hi harshadrokade ,
If you are using the latest version of Power BI you can use the Dynamic Format, create a measure and on the format select Dynamic then add the following code:
SWITCH ( TRUE (), SELECTEDVALUE ( 'Table'[Attribute] ) IN { "Parameter2", "Parameter4", "Parameter6" }, "#.0%", "#" )https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings
7 Replies
Hi harshadrokade ,
If you are using the latest version of Power BI you can use the Dynamic Format, create a measure and on the format select Dynamic then add the following code:
SWITCH ( TRUE (), SELECTEDVALUE ( 'Table'[Attribute] ) IN { "Parameter2", "Parameter4", "Parameter6" }, "#.0%", "#" )https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings
Thanks a ton MFelix . I see you could keep the % formatting of the column as Text in the power query from the first step itself. In my case, when I upload my data in Power bi & see it in Power query, the % columns automatically get converted into decimals & so the % icon doesn't remain with them. That's why I also cant use Text Dax to find the columns having % in them.
In my case, when I unpivot the parameter columns, the % format goes away & it becomes decimals.. As the format changes for % values, my Fax new column cant identify the % columns as those are not Text columns with % in them.
Hi harshadrokade,
After the unpivoting see if the step for the changed type is added if yes delete that step and add the custom column then redo the formatting.
MFelix amitchandak v-xiaotang Ashish_Mathur Greg_Deckler Anonymous bill_lalonde Wastidium
Can u pls help here
Thanks MFelix This was very very helpful & I will use the same. Just one query- I have huge around 100 parameters which will be in % format. Will DAX format formula accept so many field names into the same?
But this was really good. Made my day 🙂 Thanks again
Hi harshadrokade,
Power BI will accept, however being so many cases the best option is to create a new column on your model that defines in text the values for each format and then use that column on format in this case I have created a step on power query befor making the type format:
Has you can see if I have percentages then I do the percentage format.
Then I change the format dax to:
IF( HASONEVALUE('Table'[Attribute]), SELECTEDVALUE('Table'[Formatting String]))Result below:
No need to write any of your formula, in the future if you want to update the syntax you just need to change it in Power Query for example:
No I have two decimals without the need to change all the formula.
PBIX attach.
