cancel
Showing results for
Did you mean:
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 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?

1 ACCEPTED SOLUTION
Community Champion

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

Lima - Peru
4 REPLIES 4
Community Champion

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

Lima - Peru
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:

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

Announcements

#### Happy New Year from Power BI

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