topic Re: How to create composition ratio in powerBI in DAX Commands and Tips

How to create composition ratio in powerBI

gaccho_na — Tue, 30 Aug 2022 06:40:43 GMT

Hello,

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

Too difficult for me.

Please Help 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

Re: How to create composition ratio in powerBI

Smalfly — Tue, 30 Aug 2022 07:12:57 GMT

Hi gaccho_na ,

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

Re: How to create composition ratio in powerBI

daXtreme — Tue, 30 Aug 2022 08:44:52 GMT

// 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