Grouped sum based on each phase field - Direct Query

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[lbendlin]

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.

[Anonymous]

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