Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix @amitchandak @v-xiaotang @Ashish_Mathur @Greg_Deckler @v-rzhou-msft @bill_lalonde @Wastidium
Can u pls help here
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |