cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Percentage of subtotal

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 batchDepartmentProductCost of MaterialCost of Machine
1CuttingA1215
1AssemblyA1110
2CuttingA1410
2AssemblyA1612

 

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

ProductionDepartmentProductCost of Material%Cost of MaterialCost of Machine%Cost of MachineTotal Cost% of Total Cost
1CuttingA1252%1560%2756%
1AssemblyA1148%1040%2144%
   23100%25100%48100%

 

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?

1 ACCEPTED SOLUTION
Community Champion
Community Champion

hi @Hascins

 

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

matrix.png

 

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

 




Lima - Peru

View solution in original post

4 REPLIES 4
Community Champion
Community Champion

hi @Hascins

 

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

matrix.png

 

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

 




Lima - Peru

View solution in original post

Community Support
Community Support

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:

Capture.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors