cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
101Mathew
Resolver II
Resolver II

Custom Format - via Tabular Editor (thousands)

Hi all - I'm still learning (lots), I'm having trouble getting my calculation group for current and previous year to format in thousands to this £123k, from this (£123,123.00).

 

I'm sure this is simple to do, but after reading looking online, and see possible solution I'm still unable to resolve. Could I please ask for help?

 

"£"##0,"k";-"£"##0, does not give the required results

 

Have I missed something simple, and I being simple?

 

Thank you for look at this

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @101Mathew ,

 

If I understand correctly you are setting the format string expression for the Calculation Item? I believe you need to wrap the expression in quotation marks for calculation items - see Matt Allington's blog post as an example https://exceleratorbi.com.au/dynamic-formatting-of-switch-measures/

Where the expression already has quotation marks in it I've had success replacing them with /. So for some calculation items I've had to go from "£"#,0.00;-"£"#,0.00;"£"#,0.00 to "\£#,0.00;-\£#,0.00;\£#,0.00" - I believe this would work in your example if you put the required amount of decimal places in?

 

Not sure if that's what you are after

Tom

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

Hi @101Mathew ,

 

If I understand correctly you are setting the format string expression for the Calculation Item? I believe you need to wrap the expression in quotation marks for calculation items - see Matt Allington's blog post as an example https://exceleratorbi.com.au/dynamic-formatting-of-switch-measures/

Where the expression already has quotation marks in it I've had success replacing them with /. So for some calculation items I've had to go from "£"#,0.00;-"£"#,0.00;"£"#,0.00 to "\£#,0.00;-\£#,0.00;\£#,0.00" - I believe this would work in your example if you put the required amount of decimal places in?

 

Not sure if that's what you are after

Tom

View solution in original post

101Mathew
Resolver II
Resolver II

@FrankAT and @Greg_Deckler  - thank you both so much for your time looking at this.

 

Your solutions works, but not quite what I'm looking for

 

1 If I can format calculation group item, I need to affect less measure

2 If I this can be done via calculation group script formatting, I can have my base value formatted differently

3 Ideally I would love to get the k on the number like £123k to denote £123,123.12

 

I thank you again for your reply (which is still very useful) 

Hi @101Mathew ,

 

Consider a card showing total rows as follows:

Pragati11_0-1600695888632.png

 

Suppose I want to show the number in thousands, so I can use the format options for this visual to do so:

Pragati11_1-1600695958599.png

 

We will something as below:

Pragati11_2-1600695989856.png

 

This can be modified against number of decimal places as well.

 

Is this what you are looking for?

 

Thanks,

Pragati

 


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




@Pragati11  - Thank you kindly

 

Again a solution but not one that meets the requirements.

 

Please see my last reply

 

Again thank you so much to helping 😉

 

Regards,

Mathew

Hi @101Mathew ,

 

May be I don't understand your last response properly.

Do you want this with ZERO decimal places?

 

Add some screenshots to your issue not just the formatting you are looking for. Screenshots sometimes make things clear or may be explain your self again as I don't understand your last response properly.

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Hi @Pragati11 ,

 

But the below does not show the full intent

101Mathew_0-1600698301725.png

 

Using tabular editor, allows calculation groups within Power BI models - On each element of the calculation group, tabular editor lets you use format scripts

 

The question is do you get the above, via custom format within tabular editor

101Mathew_1-1600698479564.png

 

Hi @101Mathew ,

 

In Power BI, under Model tab, you do have an option to give custom formats to columns like shown below:

Pragati11_0-1600698959781.png

 

But not sure if this solves your issue.

 

Thanks,

Pragati

 


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




@Pragati11 Not really - but a the custom format that works on that might also work on the tabular editor format string

Hi @101Mathew ,

 

Yes I think it should work.

Have you resolved your issue?

Can you mark the appropriate solutions to this thread so others can benefit from it?

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




FrankAT
Community Champion
Community Champion

Hi @101Mathew 

take a look at the following solution:

 

21-09-_2020_15-06-20.png

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

Greg_Deckler
Super User
Super User

@101Mathew If I remember correctly when this came up before, you will need to create a measure that divides by 1000 and then use the specified format.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.