Income statement - wanting to include gross margin % row (doesnt need to be calculated)

[EvaHello]

Hi all,

I have some sample data for an income statement.  However in my matrix, i cant format the % lines easily.  They dont need to be a calculation as i have it in my source data but they need to be 'x%'

Ideally id like to add a % variance column as well

Im very new to power BI!

Level 1	Period	MTD Actual	MTD FC	MTD PY
Net Revenue	Absolute	138.2	135.5	135.6
Direct Costs	Absolute	(85.8)	(84.3)	(85.5)
	Absolute
Gross Profit	Absolute	52.4	51.2	50.1
Gross Profit Margin	Percentage	38%	38%	37%
EBIT Margin	Percentage	52%	12%	16%

[v-sshirivolu · Accepted solution]

Hi @EvaHello ,
Thanks for reaching out to the Microsoft fabric community forum.

Create these Measures - 
Formatted MTD Actual Measure - 
Formatted MTD Actual =
VAR MetricType = SELECTEDVALUE('YourTable'[Period])
VAR Val = SELECTEDVALUE('YourTable'[MTD Actual])
RETURN
IF(
MetricType = "Percentage",
FORMAT(Val, "0%"),
FORMAT(Val, "#,##0.0")
)

Formatted MTD FC Measure - 
Formatted MTD FC =
VAR MetricType = SELECTEDVALUE('YourTable'[Period])
VAR Val = SELECTEDVALUE('YourTable'[MTD FC])
RETURN
IF(
MetricType = "Percentage",
FORMAT(Val, "0%"),
FORMAT(Val, "#,##0.0")
)

Formatted MTD PY Measure - 
Formatted MTD PY =
VAR MetricType = SELECTEDVALUE('YourTable'[Period])
VAR Val = SELECTEDVALUE('YourTable'[MTD PY])
RETURN
IF(
MetricType = "Percentage",
FORMAT(Val, "0%"),
FORMAT(Val, "#,##0.0")
)

Create % Variance Column (e.g., Actual vs FC)
% Variance Actual vs FC =
VAR MetricType = SELECTEDVALUE('YourTable'[Period])
VAR Actual = SELECTEDVALUE('YourTable'[MTD Actual])
VAR FC = SELECTEDVALUE('YourTable'[MTD FC])
RETURN
IF(
MetricType = "Percentage",
FORMAT(Actual - FC, "0%"),
FORMAT(DIVIDE(Actual - FC, FC), "0.0%")
)

Create a Matrix Visual
Rows : Level 1
Values : Add all the measures created

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team 

[EvaHello]

@v-sshirivolu could i ask one more thing, is there a way to format the % if negative to show as in brackets?

[collinq]

Hey @EvaHello ,

My response and screenshot has a different field name, but all you have to do is go to the data field and right click on it and then select "Conditional Formatting"

Then, choose Font Color.

Then, choose "rules":
And select the less than zero and greater than zero.  Since you have to do the red with a start and an end just put a bunch on "9" in there like I  have in this screenshot (the greater than for red is greater than -999999).

[EvaHello]

@SR You are an absolute genius!!!  Thank you so so so much!!!

[collinq]

HI @EvaHello ,

Welcome to the World of Power BI !!    I am not sure I am following the first issue - I am seeing the percentage in the column....is that in Power BI or is that in your sample?   If you are seeing that in the sample and not in Power BI, the solution is to format your field.  There are a few ways to do so but probably the easiest is to select the field on the right hand side of the report page:

And then, in the ribbon, select the format and select percentage:

In regards to your variance question - are you just needing to divide one field from the other, or are you trying to do something else when you say the word "variance"?

[Aqua1]

@collinq Hi!  Thank you for replying.   So in my data, i have periods along the top, metrics on the left hand side.  So I guess the question is how do I format the rows which are margin related?  I want some numbers in the column to be absolute,  some to be margin related.

I haven't a clue how to write code to get margins, so ive calculated it all in my excel and imported everything across.  Please let me know if there's a better way