Percentage of subtotal

[Hascins]

Hi all,

I'm stuck with a DAX-formula and I hope you guys can help me out. I tried the formulas I already found in a related topic, but it doesn't seem to be applicable in my case. Lets say I have the fallowing data available:

Production batch	Department	Product	Cost of Material	Cost of Machine
1	Cutting	A	12	15
1	Assembly	A	11	10
2	Cutting	A	14	10
2	Assembly	A	16	12

What i need is a result like this (I need to compute the percentages😞

Production	Department	Product	Cost of Material	%Cost of Material	Cost of Machine	%Cost of Machine	Total Cost	% of Total Cost
1	Cutting	A	12	52%	15	60%	27	56%
1	Assembly	A	11	48%	10	40%	21	44%
			23	100%	25	100%	48	100%

What i tried was to build a measure like:

Total Cost of Material = sum(table[cost of Material])

and then i thought i could do something like this:

%Cost of Material=

divide (

                    sum(table[Cost of Material]);                       // I also tried the measure "Total Cost of Material

                    sumx(all(table),[Cost of Material]);

                    0)

But I cant use [Cost of Material] it want me to use a measure.

Can someone help me out?

[Vvelarde · Accepted solution]

hi @Hascins

you can do it this using a few measures and a matrix visualization

The measures are:

%CostofMachine =
CALCULATE ( SUM ( Table2[Cost of Machine] ) )
    / CALCULATE (
        SUM ( Table2[Cost of Machine] ),
        ALLEXCEPT ( Table2, Table2[Production batch] )
    )

%CostofMaterial =
CALCULATE ( SUM ( Table2[Cost of Material] ) )
    / CALCULATE (
        SUM ( Table2[Cost of Material] ),
        ALLEXCEPT ( Table2, Table2[Production batch] )
    )

%TotalCost =
CALCULATE ( [TotalCost] )
    / CALCULATE ( [TotalCost], ALLEXCEPT ( Table2, Table2[Production batch] ) )

TotalCost =
CALCULATE ( SUM ( Table2[Cost of Machine] ) )
    + CALCULATE ( SUM ( Table2[Cost of Material] ) )

[v-shex-msft]

Hi @Hascins,

You can refer to below table formula to achieve your requirement.

Summary Table = 
SELECTCOLUMNS(
	SUMMARIZE(Sheet10,
		[Production batch],
		ROLLUP(ROLLUPGROUP([Department],[Product])),
		"Cost of Material",SUM(Sheet10[Cost of Material]),
		"Cost of Machine",SUM(Sheet10[Cost of Machine])
		), 
			"Production",[Production batch],
			"Department",[Department],
			"Product",[Product],
			"Cost of Material",[Cost of Material],
			"% Cost of Material",FORMAT( DIVIDE([Cost of Material],SUMX(FILTER(ALL(Sheet10),[Production batch]=EARLIER([Production batch])),[Cost of Material]),0),"Percent"),
			"Cost of Machine",[Cost of Machine],
			"% Cost of Machine",FORMAT(DIVIDE([Cost of Machine],SUMX(FILTER(ALL(Sheet10),[Production batch]=EARLIER([Production batch])),[Cost of Machine]),0),"Percent"),
			"Total",[Cost of Material]+[Cost of Machine],
			"% Total",FORMAT(DIVIDE([Cost of Material]+[Cost of Machine],SUMX(FILTER(ALL(Sheet10),[Production batch]=EARLIER([Production batch])),[Cost of Material]+[Cost of Machine]),0),"Percent")
	)

Result:

Notice:
1. Subtotal is based on "Production" and "Product" fields.

2. SELECTCOLUMNS function is used to add columns and modify the column index.

Regards,

Xiaoxin Sheng

[Anonymous]

I am having the same kind of requirement 

https://community.powerbi.com/t5/Power-Platform-Integrations/Subtotal-need-to-alter-based-on-row-hea...

Could you help me

[tjd]

When in a table, you shouldn't have to calculate the totals, just turn on the totals calculation.  The trick lies in creating the % measures.  If you do something like DIVIDE(x, sum(x)), you get the % of the filtered values and the total should be 100%.  If you do something like DIVIDE(x, ALL(sum(X)), you now get the % of x across all unfiltered values and the total shown on a filered table would be less than 100%.