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
BrendonHerbert
Regular Visitor

Help with Card Data Type Formats

Hi all,

 

I am working on something that is a little fun but I have reached a point where I am a little "flummoxed".

 

It is essentially a simple report based on 4Dx which allows users to define a bunch of variables in excel and then I can ingest it into Power BI and create a canned visual style report.  I am using this as an opportunity to play with Power BI so there are probably lots of things wrong here.  However....

 

I have an excel spreadsheet containing some parameters and values (see below)

 

BrendonHerbert_1-1572401913088.png

 

I then import into Power BI

 

BrendonHerbert_2-1572401986166.png

 

From here I have a Power BI Report page extracting the data using measures (mainly).

 

Sample measure and report page below.

 

BrendonHerbert_3-1572402064480.png

 

BrendonHerbert_0-1572401815849.png

 

The problem I have is that I cannot for the life of me figure out how to format the KPI target into a standard number. 

 

I have tried modifying the field format but because there are combinations of text and numeric values they all seem greyed out.

 

BrendonHerbert_5-1572402262950.png

BrendonHerbert_4-1572402229321.png

I am assuming I have done something silly but any help would be greatly appreciated. 

 

regards

 

Brendon

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

Hi @BrendonHerbert ,

 

FORMAT operates on numeric values. So FORMAT(<value> ,"Standard")  will display the value with thousand separators only if the value is of type numeric.  In your case all the values are of type text, although the content is numeric. So this approach will not work.

 

You could change the measure as follows

 

var Parameter = "KPI target"

var Stage1 = LOOKUPVALUE( Your original expression)

var Stage2 = CONVERT (Stage1, DOUBLE)

return

FORMAT(Stage2, "Standard")

 

Check it out and let me know.

 

Cheers

 

CheenuSing

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
CheenuSing
Community Champion
Community Champion

Hi @BrendonHerbert ,

 

FORMAT operates on numeric values. So FORMAT(<value> ,"Standard")  will display the value with thousand separators only if the value is of type numeric.  In your case all the values are of type text, although the content is numeric. So this approach will not work.

 

You could change the measure as follows

 

var Parameter = "KPI target"

var Stage1 = LOOKUPVALUE( Your original expression)

var Stage2 = CONVERT (Stage1, DOUBLE)

return

FORMAT(Stage2, "Standard")

 

Check it out and let me know.

 

Cheers

 

CheenuSing

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @CheenuSing  and @cjayaneththi ,

 

thank you both for your responses.  

 

@CheenuSing your approach worked perfectly!!

 

thank you very much

 

BrendonHerbert_0-1572411255321.png

cjayaneththi
Helper I
Helper I

Hi,

 

You can create separate table for numeric  values when you query this table.

 

Cheers 

 

 

 

 

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.