Forum Discussion
Grouped sum based on each phase field - Direct Query
Hello! I've been stuck on this problem for a while now so thought I would reach out. I am using a Direct Query method so I am not able to use grouping in a power query to accomplish this. Each ph...
Hi Anonymous
Create four measures to sum these fields .
SpentCmdtCost Total = CALCULATE(SUM('Table'[SpentCmdtCost]),ALLEXCEPT('Table','Table'[Phase]))NonCmdtCost Total = CALCULATE(SUM('Table'[NonCmdtCost]),ALLEXCEPT('Table','Table'[Phase]))CurrEstCost Total = CALCULATE(SUM('Table'[CurrEstCost]),ALLEXCEPT('Table','Table'[Phase]))BuyOut Total = CALCULATE(SUM('Table'[BuyOut]),ALLEXCEPT('Table','Table'[Phase]))Then add field [Phase] in a slicer , when you choose different Phase, you will get different totals .
I have attached my pbix file , you can refer to it .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for this! I can't use a slicer to separate the phases individually, I need one row per phase that adds up the cost types 1,2 and 8 in one single table. Here is another example:
| Job | Phase | CostType | SpentCmdtCost | NonCmdtCost | CurrEstCost | BuyOut | TotalCmtdCost | CurrBuyoutCost |
| 211037 | 01-040 . . | 1 | 0 | 107239.92 | 118734 | -28020.36 | 0 | 146754.36 |
| 211037 | 01-040 . . | 2 | 0 | 39508.63 | 0 | 0 | 0 | 39508.63 |
| 211037 | 01-040 . . | 8 | 0 | 8578.53 | 0 | 0 | 0 | 8578.53 |
| 211037 | 01-042 . . | 1 | 0 | 23979.68 | 31339 | -529.52 | 0 | 31868.52 |
| 211037 | 01-042 . . | 2 | 0 | 7900.41 | 0 | 0 | 0 | 7900.41 |
| 211037 | 01-042 . . | 8 | 0 | 1918.38 | 0 | 0 | 0 | 1918.38 |
| 211037 | 01-043 . . | 1 | 0 | 41254.4 | 33443 | -22031.27 | 0 | 55474.27 |
| 211037 | 01-043 . . | 2 | 0 | 12992.76 | 0 | 0 | 0 | 12992.76 |
| 211037 | 01-043 . . | 8 | 0 | 3300.12 | 0 | 0 | 0 | 3300.12 |
| 211037 | 01-044 . . | 1 | 0 | 9295.99 | 8049 | -5423.23 | 0 | 13472.23 |
| 211037 | 01-044 . . | 2 | 0 | 3266.7 | 0 | 0 | 0 | 3266.7 |
| 211037 | 01-044 . . | 8 | 0 | 743.54 | 0 | 0 | 0 | 743.54 |
| 211037 | 01-049 . . | 1 | 0 | 2318.04 | 0 | -3260.69 | 0 | 3260.69 |
| 211037 | 01-049 . . | 2 | 0 | 757.12 | 0 | 0 | 0 | 757.12 |
| 211037 | 01-049 . . | 8 | 0 | 185.53 | 0 | 0 | 0 | 185.53 |