cancel
Showing results 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

New Member

How to create composition ratio in powerBI

Hello,

I want to display output table B from data table A.

Too difficult for me.

data table A

 Date DIV value 2020/04 Net Sales 600,000 2020/04 Cost of Sales 300,000 2020/04 Gross Profit 300,000 2020/04 SGA expenses 120,000 2020/04 Operating income 180,000 2020/05 Net Sales 800,000 2020/05 Cost of Sales 390,000 2020/05 Gross Profit 410,000 2020/05 SGA expenses 125,000 2020/05 Operating income 285,000 2020/06 Net Sales 500,000 2020/06 Cost of Sales 260,000 2020/06 Gross Profit 240,000 2020/06 SGA expenses 120,000 2020/06 Operating income 120,000

output table B

 TTL Value composition ratio Net Sales 1,900,000 100.0% Cost of Sales 900,000 47.4% Cost of Sales / Net Sales Gross Profit 1,000,000 52.6% Gross Profit / Net Sales SGA expenses 400,000 21.1% SGA expenses / Net Sales Operating income 600,000 31.6% Operating income / Net Sales

1 ACCEPTED SOLUTION
Solution Sage
``````// Base measure
[Total Value] = SUM( 'Table'[Value] )

[Composition Ratio] =
var NetSalesAmount =
CALCULATE(
[Total Value],
'Table'[DIV] = "Net Sales"
)
var Result =
DIVIDE(
[Total Value],
NetSalesAmount
)
return
Result``````
2 REPLIES 2
Solution Sage
``````// Base measure
[Total Value] = SUM( 'Table'[Value] )

[Composition Ratio] =
var NetSalesAmount =
CALCULATE(
[Total Value],
'Table'[DIV] = "Net Sales"
)
var Result =
DIVIDE(
[Total Value],
NetSalesAmount
)
return
Result``````
Resolver IV

Hi @gaccho_na ,

please have a look at this expample file and let me know if this helps you to solve your issue.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.