Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ArchStanton
Post Prodigy
Post Prodigy

Cannot remove decimal places

Hi,

 

This is driving me mad, hope someone can help!

The code below defaults the Average Months to 2 decimal places - I don't want any decimals.

 

Case Length (Adj) Mths = 
IF (
    'Cases'[statecode] = "Active",
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        NOW (),
        MONTH
    ),
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        'Cases'[Resolution Date],
        MONTH
    )
)

 

ArchStanton_0-1707481170559.png

 

I have tried so many solutions and none work.

1 ACCEPTED SOLUTION

@ArchStanton 

Measure = FORMAT(SUM(Table[Case Length (Adj) Mths]),"##")






Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





View solution in original post

10 REPLIES 10
Daniel29195
Super User
Super User

@ArchStanton 

use format (  your_measure_code , "##")

 

let me know if this works .

 

 

 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Hi,

 

Using FORMAT breaks the code and my Average average disappears: 

 

ArchStanton_1-1707482351744.png

 

ArchStanton_0-1707482274295.png

 

Case Length (Adj) Mths = FORMAT(
IF (
    'Cases'[statecode] = "Active",
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        NOW (),
        MONTH
    ),
    DATEDIFF (
        IF (
            ISBLANK ( 'Cases'[legacycasecreationdate] ),
            'Cases'[Created On],
            'Cases'[legacycasecreationdate]
        ),
        'Cases'[Resolution Date],
        MONTH
    )
),"##")

 

 

 

 

PijushRoy
Super User
Super User

Hi @ArchStanton 

Please use a measure 

 

Measure = FORMAT([YourMeasure],"##")


Let me know if that works for you

 


If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
Linkedin




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





@ArchStanton 

 

convert(format( your_measure, "##" ) ,integer) 

 

 

pattern : 

Daniel29195_0-1707483435512.png

 

Thanks Daniel, I managed to get the answer from the other helper on this thread, I was dealing with a calculated column not a Measure.

Thank you for helping anyway.

Hi, I've responded to Daniel above who proposed the same solution. It does not work as I am summarising the calaculated column by 'Averaging'.

 

FORMAT breaks the code and I do not have the option to see a Summarised value anymore only Counts

Hi @ArchStanton 

Dont change your existing measure
Create a another measure use 

Measure = FORMAT([Case Length (Adj) Mths],"##")

 

If your requirement is solved, please mark THIS ANSWER as SOLUTION

 




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





Case Length (Adj) Mths is a calculated column not a measure

 

It was initially calculating DAYS but I created a copy and changed the Parameter to MONTH earlier today

@ArchStanton 

Measure = FORMAT(SUM(Table[Case Length (Adj) Mths]),"##")






Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





Hi,

Thanks for helping me with this.

It didn't work initially because the Calculated Column is summarised as an Average.

I removed this from the column, substituted SUM for AVERASGE and it now works with no decimal places 😊

It's incredible that so much effort and resource is required to do a simple thing like this - maybe its something for the ideas board?!

 

Thanks for your help!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.