Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
harshadrokade
Post Partisan
Post Partisan

Formatting values separately in unpivot value column

Hi All,

 

I have below data-

CategoryLocationParameter1Parameter2Parameter3Parameter4Parameter5Parameter6
C1L1255%542%2419%
C2L2488%333%998%
C3L3913%131%6315%
C4L4775%322%1920%
C5L5373%721%6334%
C6L6575%351%287%
C7L7193%481%9215%
C8L8718%962%3310%
C9L9964%231%296%
C10L10364%691%466%

 

As I want to create a column chart along with a parameter selection filter, I unpivot the data as below.

 

harshadrokade_0-1684229121896.png

 

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_1-1684229394372.png

 

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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%",
    "#"
)

 

MFelix_0-1684273549383.pngMFelix_1-1684273556839.png

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
harshadrokade
Post Partisan
Post Partisan

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:

 

MFelix_0-1684395145168.png

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:

MFelix_3-1684395309493.png

 

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:

MFelix_2-1684395262959.png

MFelix_4-1684395336548.png

No I have two decimals without the need to change all the formula.

 

PBIX attach.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

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%",
    "#"
)

 

MFelix_0-1684273549383.pngMFelix_1-1684273556839.png

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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. 

harshadrokade_0-1684415917278.png

 

 

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. 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

harshadrokade
Post Partisan
Post Partisan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.