Calculated row item in Matrix (Gross Margin% ratio)

[Takazeki]

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?

[audreygerred · Accepted solution]

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).

[Takazeki]

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)