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

Reply
Takazeki
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 CenterAccount GroupP&L ItemMonthValue
#1#AccountGroup1Revenue10
#1#AccountGroup2Personnel Costs1200
#1#AccountGroup3Operating Costs1550
#1#AccountGroup4Gross Margin1-750
#1#AccountGroup5Gross Margin%1infinity
#1#AccountGroup1Revenue21000
#1#AccountGroup2Personnel Costs2200
#1#AccountGroup3Operating Costs2400
#1#AccountGroup4Gross Margin2400
#1#AccountGroup5Gross Margin%240%
(…)(…)(…)(…) 
#1#AccountGroup1Revenue120
#1#AccountGroup2Personnel Costs12200
#1#AccountGroup3Operating Costs12300
#1#AccountGroup4Gross Margin12800
#1#AccountGroup5Gross Margin%12infinity

 

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) 
Revenue10000(Shows the sum of Month 1 to 12)
Personnel Costs3000(Shows the sum of Month 1 to 12)
Operating Costs4000(Shows the sum of Month 1 to 12)
Gross Margin3000(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
audreygerred
Super User
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!





View solution in original post

2 REPLIES 2
audreygerred
Super User
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!





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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

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