Removing decimals from values while keeping for totals

[Justas4478]

Hi, I have table that has some counts.
However I cant find a way how I could remove delimal points from values in red square while keeping decimal points for values in green square.
I tried couple options but that did not seam to work for me.
Does anyone know any solution if that is even possible?

[danextian]

Hi @Justas4478 

Apply dynamic format string to the measure being used in the viz. In the screenshot below, "#,#.00" is being applied to the column total wherein geo has no single value (as it has multiple at the total which are the indivdual geos) otherwise, "#,#".

[Justas4478]

@danextian I tried your solution as well, but same problem it requires column while I can only provide measure.

[danextian]

Apply dynamic format string to a measure but reference the column in your formula. You are referencing the measure the string is to be applied to and not to the dates.

[Justas4478]

@danextian I quickly looked in to and it seasm I cant create dinamic format since it is on for model measures and not ones using live connection.

[danextian · Accepted solution]

Unfortunately, with live connection, you will need to apply the dynamic format string to the measure in the semantic model itself. Alternatively, you can create another measure that returns a formatted text instead

IF (
    NOT ( HASONEVALUE ( Geo[Geo] ) ),
    FORMAT ( [my measure], "#,#.00" ),
    FORMAT ( [my measure], "#,#" )
)

[Justas4478]

@danextian It works thanks for the solution.
To bad that it doubles refresh time.

But I guess this is only option I have.

[saud968]

Unfortunately, directly removing decimal points from specific cells while keeping them for others within a single table visual in Power BI Desktop isn't possible.

However, here are a couple of effective workarounds to achieve the desired result:

1. Conditional Formatting:

Create a Measure:
Write a measure to determine if a value is a total or a detail value. For example:

IsTotal = IF(
HASONEVALUE('YourTableName'[YourGroupingColumn]),
FALSE,
TRUE
)
Apply Conditional Formatting:
Select the visual where you want to apply the formatting.
Go to the Format pane.
Under Conditional formatting, create a new rule.
Set the rule to format values where IsTotal is TRUE.
In the Format pane for the rule, set the number format to "Number" with zero decimal places.
2. Create a Separate Visual:

Create a New Visual:
Add a new visual (e.g., table or matrix) to your report.
Use the same data as the original visual, but filter it to only show the total values.
Apply the desired formatting (zero decimal places) to the total values in this new visual.
Position the Visuals:
Position the two visuals side by side or one below the other, aligning them to create a seamless appearance.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Justas4478]

@saud968 The measure you provide requires column while I am using measure as my data.
Here is measure:

Dispatch average =

AVERAGEX(

    KEEPFILTERS(VALUES('Date'[Week Start Date])),

    CALCULATE('Stock Movement'[Dispatch count])

)

Is there way to change where I can use measure?

[saud968]

Try a possible approach using a calculated column:

Create a Calculated Column:

Add a new calculated column to your 'Date' table:

IsTotal = IF(
HASONEVALUE('Date'[Week Start Date]),
FALSE,
TRUE
)
Modify Your Measure:

Modify your Dispatch average measure to incorporate the IsTotal column:

Dispatch average =
IF(
MAX('Date'[IsTotal]),
FORMAT(
AVERAGEX(
KEEPFILTERS(VALUES('Date'[Week Start Date])),
CALCULATE('Stock Movement'[Dispatch count])
),
"0"
),
AVERAGEX(
KEEPFILTERS(VALUES('Date'[Week Start Date])),
CALCULATE('Stock Movement'[Dispatch count])
)
)
This modified measure will format the total value as an integer and display the detail values with decimal places.

Alternative Approach Using a Second Measure:

Create a Second Measure:

Create a new measure to calculate the formatted total:

Formatted Total Dispatch =
FORMAT(
AVERAGEX(
KEEPFILTERS(VALUES('Date'[Week Start Date])),
CALCULATE('Stock Movement'[Dispatch count])
),
"0"
)
Use the Measures in Your Visual:

In your table or matrix visual, use the Dispatch average measure for the detail rows and the Formatted Total Dispatch measure for the total row.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Justas4478]

@saud968Unfortunatelly since company uses live connection method.
I am limited to only being able to create measures.
So I cant do any calculated columns.