Forum Discussion
Solved
DAX measure Suggestion
Hello All, I would like to ask a measure formula because I couldn't write properly. I miss some little details but I can't see where. You can see the table below. I add some Letters on top t...
Hi ahmetturetmis,
You can use following calculate table formula to create a new table that summary raw table value, it should dynamically change based on the raw 'calculation table':
summary = GROUPBY ( SUMMARIZE ( T2, [FROM CBS], [FOCUS CBS], [MH], "Rate", [Final Weightage] * [MH] ), [FOCUS CBS], "Total", SUMX ( CURRENTGROUP (), [Rate] ) )Regards,
Xiaoxin Sheng
I've prepared a dummy table :
Data table this is changing everyday.
| Date | CBS | Quantity | MH |
| 01-09-20 | A | 1 | 12 |
| 01-09-20 | B | 2 | 15 |
| 01-09-20 | C | 3 | 18 |
| 02-09-20 | D | 4 | 21 |
| 03-09-20 | E | 5 | 24 |
| 02-09-20 | F | 6 | 27 |
| 02-09-20 | G | 7 | 30 |
| 02-09-20 | H | 8 | 33 |
| Total | 180 |
This is a Importance matrix
| A | B | E | |
| A | 1 | ||
| B | 1 | ||
| C | 2 | 1 | |
| D | 1 | 1 | |
| E | 1 | ||
| F | 1 | 1 | |
| G | 1 | ||
| H | 1 |
This is the Calculation Table
| FROM CBS | FOCUS CBS | FROM CBS MH | FROM CBS QTY | QTY WEIGHT | IMPORTANCE | IMPORTANCE WEIGHT | QTY WEIGH X IMPORTANCE WEIGHT | Totals of Combination | Final Weightage | MH | DISTRIBUTED MH |
| A | A | 12 | 1 | 100% | 1 | 100% | 100% | 100% | 100% | 12 | 12.00 |
| B | B | 15 | 2 | 100% | 1 | 100% | 100% | 100% | 100% | 15 | 15.00 |
| C | A | 18 | 1 | 17% | 2 | 67% | 11% | 39% | 29% | 18 | 5.14 |
| C | E | 18 | 5 | 83% | 1 | 33% | 28% | 39% | 71% | 18 | 12.86 |
| D | A | 21 | 1 | 100% | 1 | 50% | 50% | 64% | 78% | 21 | 16.33 |
| D | B | 21 | 2 | 29% | 1 | 50% | 14% | 64% | 22% | 21 | 4.67 |
| E | E | 24 | 5 | 71% | 1 | 100% | 71% | 71% | 100% | 24 | 24.00 |
| F | A | 27 | 1 | 33% | 1 | 50% | 17% | 50% | 33% | 27 | 9.00 |
| F | B | 27 | 2 | 67% | 1 | 50% | 33% | 50% | 67% | 27 | 18.00 |
| G | E | 30 | 5 | 100% | 1 | 100% | 100% | 100% | 100% | 30 | 30.00 |
| H | B | 33 | 2 | 100% | 1 | 100% | 100% | 100% | 100% | 33 | 33.00 |
| Total | 180 |
This is the Summary of Focus CBS
| FOCUS CBS | DISTRIBUTED MH |
| A | 42.48 |
| B | 70.67 |
| E | 66.86 |
| Total | 180 |
This calculations are changing with the new quantity and mh values added.
Hi ahmetturetmis,
You can use following calculate table formula to create a new table that summary raw table value, it should dynamically change based on the raw 'calculation table':
summary = GROUPBY ( SUMMARIZE ( T2, [FROM CBS], [FOCUS CBS], [MH], "Rate", [Final Weightage] * [MH] ), [FOCUS CBS], "Total", SUMX ( CURRENTGROUP (), [Rate] ) )Regards,
Xiaoxin Sheng