Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Percentage of subtotal

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Percentage of subtotal

11-29-2016
09:37 AM

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?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2016
08:29 PM

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

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2016
08:29 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-30-2016
06:44 PM

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.

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-07-2020
11:34 PM

I am having the same kind of requirement

Could you help me

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-29-2016
11:07 AM

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

362 | |

197 | |

67 | |

66 | |

45 |