Formatting values separately in unpivot value column

[harshadrokade]

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? 

[harshadrokade]

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

[MFelix]

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.

[MFelix · Accepted solution]

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

[harshadrokade]

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. 

[MFelix]

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. 

[harshadrokade]

Thanks a ton @MFelix . Your help was very useful..

[harshadrokade]

@MFelix @amitchandak @v-xiaotang @Ashish_Mathur @Greg_Deckler @v-rzhou-msft @bill_lalonde @Wastidium 

Can u pls help here