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 phase would have one row with the columns summarized for cost types 1,2,8.
Below is a sample data set and then the totals I would expect to see for the Phase. Any help is appreciated, thank you!
| 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 |
Phase 01-040 Totals:
- SpentCmdtCost = 0
- NonCmdtCost = 152,469.51
- CurrEstCost = 118,734.00
- BuyOut = 28,020.36
Phase 01-042 Totals:
- SpentCmdtCost = 0
- NonCmdtCost = 33,353.77
- CurrEstCost = 31,399.00
- BuyOut = -529.52
4 Replies
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.
Here is the data:
Job Phase CostType SpentCmdtCost NonCmdtCost CurrEstCost BuyOut TotalCmtdCost CurrBuyoutCost 211037 01-040 . . 1 0 107239.9 118734 -28020.4 0 146754.4 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
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
