Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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] ) )
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] ) )
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
I am having the same kind of requirement
Could you help me
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%.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |