DAX Adjustment for negative % values

[Emma_]

Hello,

I would like to make a correction for my % values displayed on the bar chart as additional labels (details labels) so that we do not see any negative values or values above 100%. Absolute values should be displayed as they are (actuals). Does anyone know how to fix this in DAX? Thank you for help.

	actuals	positive value adjusted only for % calculation purpose (intermediate step)	% actuals	% positiv value adjusted (final result)
Client Group 1	-35'106	35'106	-18.2	13.3
Client Group 2	228'283	228'283	118.2	86.7
Total	193'177	263'389

[burakkaragoz · Accepted solution]

Hi @Emma_ ,

You need to create an adjusted percentage measure that caps values between 0% and 100%. Here's the DAX solution:

Create this measure for your adjusted percentage:

Adjusted Percentage = 
VAR TotalValue = SUM(YourTable[Total])
VAR CurrentValue = SUM(YourTable[Actuals])
VAR RawPercentage = DIVIDE(CurrentValue, TotalValue, 0)
VAR AdjustedPercentage = 
    SWITCH(
        TRUE(),
        RawPercentage < 0, 0,
        RawPercentage > 1, 1,
        RawPercentage
    )
RETURN AdjustedPercentage

Alternative cleaner version:

Capped Percentage = 
VAR RawPct = DIVIDE([Actuals], [Total], 0)
RETURN 
MIN(MAX(RawPct, 0), 1)

This uses MIN/MAX functions to cap the percentage between 0 and 1 (0% and 100%).

For your bar chart labels:

• Keep using your original [Actuals] measure for the bar values
• Use the new [Adjusted Percentage] measure for the data labels
• Format the percentage measure to show as percentage with desired decimal places

Quick tip: If you want to show both actual values and adjusted percentages as labels, you can create a combined measure:

Combined Label = 
[Actuals] & " (" & FORMAT([Adjusted Percentage], "0.0%") & ")"

This way your negative actuals still show as-is, but percentages are properly capped for visual clarity.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[Emma_]

Thank you.

[DataNinja777 · Accepted solution]

Hi @Emma_ ,

To fix your percentage display, you can create a DAX measure that adjusts the calculation to show a category's contribution to a total made of only positive values. This approach ensures no percentage is negative or that the total exceeds 100%. The final result is the proportion of each category's absolute value against the sum of all absolute values.

Here is the DAX measure to achieve this. You should replace 'YourTable'[Client Group] with the actual column from your data model that contains the categories. Also, replace [Actuals] if your base measure has a different name.

% Adjusted = 
VAR _AbsoluteActual =
    ABS ( [Actuals] )
VAR _TotalAbsoluteSum =
    SUMX ( ALLSELECTED ( 'YourTable'[Client Group] ), ABS ( [Actuals] ) )
VAR _Result =
    DIVIDE ( _AbsoluteActual, _TotalAbsoluteSum )
RETURN
    _Result

This formula works by first using a variable, _AbsoluteActual, to get the positive equivalent of your [Actuals] value for the current category on the chart. Next, the _TotalAbsoluteSum variable calculates the new denominator. It does this by using SUMX to iterate over all selected client groups (ALLSELECTED) and sums their absolute values, effectively creating the 263,389 total from your example. Finally, the formula safely divides the category's absolute value by the total sum of absolute values.

After creating this new measure, you must format it as a percentage using the "Measure tools" in Power BI. You can then use this % Adjusted measure as the data label on your bar chart to display the corrected values.

Best regards,

[burakkaragoz · Accepted solution]

Hi @Emma_ ,

You need to create an adjusted percentage measure that caps values between 0% and 100%. Here's the DAX solution:

Create this measure for your adjusted percentage:

Adjusted Percentage = 
VAR TotalValue = SUM(YourTable[Total])
VAR CurrentValue = SUM(YourTable[Actuals])
VAR RawPercentage = DIVIDE(CurrentValue, TotalValue, 0)
VAR AdjustedPercentage = 
    SWITCH(
        TRUE(),
        RawPercentage < 0, 0,
        RawPercentage > 1, 1,
        RawPercentage
    )
RETURN AdjustedPercentage

Alternative cleaner version:

Capped Percentage = 
VAR RawPct = DIVIDE([Actuals], [Total], 0)
RETURN 
MIN(MAX(RawPct, 0), 1)

This uses MIN/MAX functions to cap the percentage between 0 and 1 (0% and 100%).

For your bar chart labels:

• Keep using your original [Actuals] measure for the bar values
• Use the new [Adjusted Percentage] measure for the data labels
• Format the percentage measure to show as percentage with desired decimal places

Quick tip: If you want to show both actual values and adjusted percentages as labels, you can create a combined measure:

Combined Label = 
[Actuals] & " (" & FORMAT([Adjusted Percentage], "0.0%") & ")"

This way your negative actuals still show as-is, but percentages are properly capped for visual clarity.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.