Forum Discussion
Post ProdigyRemoving decimals from values while keeping for totals
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], "#,#" ) )
Memorable MemberUnfortunately, 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!
Justas4478saud968 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.