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
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

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

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.