Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculated row item in Matrix (Gross Margin% ratio)

I'm having a hard time calculating a simple financial ratio inside my Matrix in PowerBI.

This is how I kind of have my data when I look into Power Query. Gross Margin% is a Conditional Column that I unpivotted, so that I could have data this way:

 Cost Center Account Group P&L Item Month Value #1 #AccountGroup1 Revenue 1 0 #1 #AccountGroup2 Personnel Costs 1 200 #1 #AccountGroup3 Operating Costs 1 550 #1 #AccountGroup4 Gross Margin 1 -750 #1 #AccountGroup5 Gross Margin% 1 infinity #1 #AccountGroup1 Revenue 2 1000 #1 #AccountGroup2 Personnel Costs 2 200 #1 #AccountGroup3 Operating Costs 2 400 #1 #AccountGroup4 Gross Margin 2 400 #1 #AccountGroup5 Gross Margin% 2 40% (…) (…) (…) (…) #1 #AccountGroup1 Revenue 12 0 #1 #AccountGroup2 Personnel Costs 12 200 #1 #AccountGroup3 Operating Costs 12 300 #1 #AccountGroup4 Gross Margin 12 800 #1 #AccountGroup5 Gross Margin% 12 infinity

What I need to achieve is having a Matrix that summarizes the P&L Items (in row) and the Cost Centers in Column, showing as Sum of Value the accumulated data for the 12 months. Everything works great except for the Gross Margin%, which I keep getting "Nan":

 P&L Item #1 (Cost Center) Revenue 10000 (Shows the sum of Month 1 to 12) Personnel Costs 3000 (Shows the sum of Month 1 to 12) Operating Costs 4000 (Shows the sum of Month 1 to 12) Gross Margin 3000 (Shows the sum of Month 1 to 12) Gross Margin% Nan (Should show the ratio calculated between the Sum of Gross Margin and the Sum of Revenue)

Does anybody know how I can achieve this?

1 ACCEPTED SOLUTION
Super User

Is there a way you can have additional data points added to your file? It is better to utilize measures here.

For example, if you have Revenue and Cost you can create a measure to calculate Profit. For example, Profit = SUM('Fact'[Revenue]) - SUM('FACT'[Cost]), or if you had explicit measures for Revenue and Cost it would be Profit = [Revenue]-[Cost].

I see you already have Profit in your data (assuming that is Margin), so for Gross Profit Margin you would still need Revenue added to your data and your measure would be Gross Profit Margin = DIVIDE([Profit],[Revenue]) (assuming you have explicit measures for Profit and Revenue).

I would similary make explicit measures for the rest of your items (Personal Costs, Operation Costs, etc), then when you put the values into a matrix, switch Values to rows in the formatting for that visual (found under Options in Values).

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

2 REPLIES 2
Super User

Is there a way you can have additional data points added to your file? It is better to utilize measures here.

For example, if you have Revenue and Cost you can create a measure to calculate Profit. For example, Profit = SUM('Fact'[Revenue]) - SUM('FACT'[Cost]), or if you had explicit measures for Revenue and Cost it would be Profit = [Revenue]-[Cost].

I see you already have Profit in your data (assuming that is Margin), so for Gross Profit Margin you would still need Revenue added to your data and your measure would be Gross Profit Margin = DIVIDE([Profit],[Revenue]) (assuming you have explicit measures for Profit and Revenue).

I would similary make explicit measures for the rest of your items (Personal Costs, Operation Costs, etc), then when you put the values into a matrix, switch Values to rows in the formatting for that visual (found under Options in Values).

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

Thanks for the suggestion. I ended up working with pivotted data (with P&L items in columns) and then I created calculated measures to deal with calculations like Gross Margin and Gross Margin%.

From there, I created a calculated table to unpivotted the data in the way I needed to build the Matrix in the correct way:

``````SAP_COSP+FCST_Current-Unpivot =
VAR t1 =
SELECTCOLUMNS ( 'SAP_COSP+FCST_Current'; "Cost Center"; [CC - Short Name]; "CC Type"; [CC Type]; "TU"; [TU]; "BL"; [BL];"Measure"; "Revenue"; "Value"; [TOTAL REVENUE] )
VAR t2 =
SELECTCOLUMNS ( 'SAP_COSP+FCST_Current'; "Cost Center"; [CC - Short Name]; "CC Type"; [CC Type]; "TU"; [TU]; "BL"; [BL];"Measure"; "Personnel Costs"; "Value"; [PERSONNEL COSTS] )
(...)
RETURN
UNION ( t1; t2; t3; t4; t5; t6; t7; t8; t9; t10; t11; t12; t13; t14; t15; t16; t17; t18; t19; t20)``````

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors