P&L variances (formatting % and absolute values)

[Aqua1]

Hi all,

Very new to power bi but trying to build a p&l with year on year variances on the side.

Im taking my data from excel, so rows are all my metrics eg revenue etc.  Columns are the values for each year.  I have created the variance measure column to say 2025 data less 2024 data.

My question is, how do I format the variances column so it's absolute when it's values and then it's % when it's a margin %.  Do I do in the calculation?

[rajendraongole1 · Accepted solution]

Hi @Aqua1  - No, Power BI does not allow dynamic formatting of a measure to switch between number and percentage in the same column based on row context.

If you can share a small sample of your data, with sensitive information

Use FORMAT() for Mixed Display (as text)

YoY Variance Formatted =
VAR MetricName = SELECTEDVALUE('Metrics'[Metric])
VAR Diff = [2025 Value] - [2024 Value]
VAR Percent = DIVIDE(Diff, [2024 Value])
RETURN
SWITCH(
TRUE(),
MetricName IN {"Gross Margin %", "Net Profit %"}, FORMAT(Percent, "0.0%"),
FORMAT(Diff, "#,0")
)

Use this only for display purposes, e.g. in a table visual—not for calculations.

hope this helps. please check.

[v-dineshya]

Hi @Aqua1 ,

Thanks for reaching out to the Microsoft fabric community forum. 

Option 1: Add a "Type" column in your data
In your Excel sheet, add a column to label each row/metric with a type like:

Value (for Revenue) & Margin or Percentage (for Gross Margin %, Net Margin %)

Example:

Metric Type 2024 2025
Revenue Value 1000 1200
Gross Margin % Margin 60% 65%

Please follow below steps.

1.Load your data into Power BI and ensure the "Type" column comes through.
2.Create a Variance Measure

Variance =
SELECTEDVALUE('YourTable'[2025]) - SELECTEDVALUE('YourTable'[2024])

3.Create a Formatting Measure (Text) to display either absolute or % based on type:

Formatted Variance =
VAR Var = [Variance]
VAR MetricType = SELECTEDVALUE('YourTable'[Type])
RETURN
SWITCH(
TRUE(),
MetricType = "Margin", FORMAT(Var, "0.0%"),
FORMAT(Var, "#,##0")
)

This will give output as:

200 (for values like revenue)

5.0% (for margins)

Note: This creates a text value, which cannot be used for sorting or aggregations. It’s great for visuals though.

Option 2: Use a separate table for formatting logic:
If you want more flexibility or are pulling data from a more normalized source (like fact/dimension tables), build a Metrics Dimension Table where you define each metric and its display type, then join it with your data.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[Aqua1]

Hi @v-dineshya , this is what i wrote (apologies for poor dax!).  So i want to say if type = absolute, then just show the variance, but if type = margin, then show as a %

Formatted variance = if(Test[Type]="Margin",format(Test[Column],"0%;(0%)",Test[Column]))

But im getting an error.....

All the below are values :

Test is the data source

Type is my column (and its shows either absolute or margin)

Column is the variance of month to budget

[v-dineshya · Accepted solution]

Hi @Aqua1 ,

Formatted Variance =
IF(
SELECTEDVALUE(Test[Type]) = "Margin",
FORMAT(Test[Column], "0.0%;(0.0%)"), -- Show as percentage
FORMAT(Test[Column], "#,##0") -- Show as number
)

Note: Use SELECTEDVALUE() instead of direct column reference (Test[Type]) because if there's more than one row in context, Power BI will throw an error. "0.0%;(0.0%)" shows a positive percent and negative percent with parentheses. "#,##0" is your default absolute number format (no decimal).

If you also want to support decimals for absolute numbers, you can do: FORMAT(Test[Column], "#,##0.0")

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[EvaHello]

Hi @v-dineshya - i put in the formula you said but the answer (0.1) is showing as a number, not a %.  Can you explain what im doing wrong?

[v-dineshya]

Hi @Aqua1 ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[danextian]

Hi @Aqua1 

Power BI does not support dynamic formatting on columns. You can write a measure instead and apply a dynamic format string.

To return the absolute value of a sum:

ABS Measure =
ABS ( SUM ( 'table'[Variance] ) )

Assuming that there is a column that indicates whether the variance is a value or a percentage:

SWITCH (
    SELECTEDVALUE ( 'table'[variance type] ),
    "Value", "#,#",
    "Percentage", "0.00%"
)

Here's how to Create dynamic format strings for measures

Make sure to add the required columns to the visual to display the correct granularity — using ABS(SUM('table'[Variance])) will also sum up all the percentages, which might lead to incorrect results if the granularity isn't properly set.

[Aqua1]

Thank you @danextian @rajendraongole1 , I'm new to power bi so my measures are just 2025 less 2024 columns.  Are you saying to set up another column flagging if that row is a % or value.  Then would i write some sort of if statement based on this ie if %, then give % format?  I've never used dynamic functionality.  I just pull data from excel as a source, then very basic calculations 

[danextian]

Power BI will not now whether a number is a percentage or a value. They're both numbers. You can create a column that checks whether  a number is less than 1 and tag it as a percentage which is of course not always true.

[Aqua1]

• @danextian thank you, what if I create a column and type in Percentage and Absolute depending which row is margin related.  Then in my variance column, can I just put an if statement saying if its %, calculate variance in this format?  Or could I create a switch saying the same?

[Aqua1]

Hi all,

Very new to power bi but trying to build a p&l with year on year variances on the side.

Im taking my data from excel, so rows are all my metrics eg revenue etc. Columns are the values for each year. I have created the variance measure column to say 2025 data less 2024 data.

My question is, how do I format the variances column so it's absolute when it's values and then it's % when it's a margin %. Do I do in the calculation?

[some_bih]

Hi @Aqua1 I do not fully understand your request, but it could be you want to apply dynamic-format-strings, co check link with example

[rajendraongole1 · Accepted solution]

Hi @Aqua1  - No, Power BI does not allow dynamic formatting of a measure to switch between number and percentage in the same column based on row context.

If you can share a small sample of your data, with sensitive information

Use FORMAT() for Mixed Display (as text)

YoY Variance Formatted =
VAR MetricName = SELECTEDVALUE('Metrics'[Metric])
VAR Diff = [2025 Value] - [2024 Value]
VAR Percent = DIVIDE(Diff, [2024 Value])
RETURN
SWITCH(
TRUE(),
MetricName IN {"Gross Margin %", "Net Profit %"}, FORMAT(Percent, "0.0%"),
FORMAT(Diff, "#,0")
)

Use this only for display purposes, e.g. in a table visual—not for calculations.

hope this helps. please check.