cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adeeln
Post Patron
Post Patron

Need help to apply Format string on Visual when 2 different format measures used same time.

Hi Experts,
I have 2 Slicers one slicer is "Show" and the second slicer is "By".In "Show" Slicer, I have Dynamic measures that changed on Selection.
And In "By" Slicer I have dimensions that changed(dynamic x-axis change) on selection. I am working On Pareto Analysis for this I have created a dynamic measure that changes the selection of "Show" Slicer. And I have also created some calculation group because of some repeat logic. My report is working fine without format strings as below.

adeeln_0-1608951877036.png

But when I apply format strings On Visual by using calculation group. This format applied on bars value this is fine, but the issue is this format also applied On Line Percentage values as shown below.

adeeln_1-1608952002918.png

I want to apply format string only on bars not on the line values because line values always in percentage and only bar values format will be changed.

I have attached my .pbix file below you can check.
Pbix File 
Thanks 
Regards 
Adeel

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@adeeln 
There is a limitation when you create multi-format measure that the data type can only be TEXT type, you can not use it in the values field. The workaround is creating a tooltip when you hover over the value, it displays the formatted value in the tooltip.

V-pazhen-msft_0-1609467127166.png

Using report tooltip pages in Power BI - Power BI | Microsoft Docs

 


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
V-pazhen-msft
Community Support
Community Support

@adeeln 
There is a limitation when you create multi-format measure that the data type can only be TEXT type, you can not use it in the values field. The workaround is creating a tooltip when you hover over the value, it displays the formatted value in the tooltip.

V-pazhen-msft_0-1609467127166.png

Using report tooltip pages in Power BI - Power BI | Microsoft Docs

 


Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @V-pazhen-msft,

Is that not possible through Calculation Group?

Like  I have applied format string through calculation group this work but this format string applied on both line and bar values.

I just want to apply this format string on bar values not on line values because line values will be in a percentage format.

I have attached my pbix file.
Pbix file 

Thanks 

Regards 

Adeel

V-pazhen-msft
Community Support
Community Support

@adeeln 

Instead of changing the format in the visual, you can add the pre-defined numeric formats to the column measure and line measure separately.

FORMAT function (DAX) - DAX | Microsoft Docs

 

V-pazhen-msft_0-1609137208726.png

 

 

Paul Zheng _ Community SZhengt Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

Hi @V-pazhen-msft,
I have applied Pre-defined numeric formats as you suggested this Pre-defined numeric format works fine On table visual but not work On bar chart. You can see in the below image after applied Pre-defined numeric formats bars not show in visuals.

KPI Value Show Switch = 

SWITCH (

SELECTEDVALUE ( 'Dynamic Selection'[Value] ),

"Creditors", [Creditors],

"Creditors (with outstanding invoices)", FORMAT([Creditors (with outstanding invoices)],"Standard"),

"Creditors (with overdue invoices)", FORMAT([Creditors (with overdue invoices)],"Standard"),

"Invoices (cleared)",FORMAT( [Invoices (cleared)],"Standard"),

"Outstanding Invoices (not cleared)", FORMAT([Outstanding Invoices (not cleared)],"Standard"),

"Overdue Invoices (cleared)", FORMAT([Overdue Invoices (cleared)],"Standard"),

"Overdue Invoices (not cleared)",FORMAT( [Overdue Invoices (not cleared)],"Standard"),

"Total Invoice Amount", FORMAT([Total Invoice Amount],"Currency"),

"Total Invoice Amount (cleared)", FORMAT([Total Invoice Amount (cleared)],"Currency"),

"Total Outstanding Amount", FORMAT([Total Outstanding Amount],"Currency"),

"Total Overdue Amount",FORMAT([Total Overdue Amount],"Currency")

)
 
Selected Measure = 

CALCULATE ( [KPI Value Show Switch] ,GroupbyCal[GroupbyCal] = "GroupbyCal" )

format siiue.png

Thanks 

Regards 
Adeel

parry2k
Super User III
Super User III

@adeeln does your original measure has a currency format? if not then change your measure format to currency format and it will work.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k  Yes my original measure in currency format.

 

parry2k
Super User III
Super User III

@adeeln image.png






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k  I have used SELECTEDMEASUREFORMATSTRING() as you suggested but by using this Currency format values not show in Currency format. By using SELECTEDMEASUREFORMATSTRING()  all values show in simple numbers format but I want to show currency values in currency format. Like the picture, you have attached in you see values that show on bars only in simple number format, not in currency(with a dollar sign) format.
Example in your attached image bar value show like "0.75M or 0.4M" These are simple numbers. These values should be in Currency format like "$0.75M Or $0.4M".
Can you please attach my pbix file in which you applied the format string?
thanks 
Regards
Adeel

parry2k
Super User III
Super User III

@adeeln use this SELECTEDMEASUREFORMATSTRING() DAX function for format string and it will work fine

 

image.png

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors