Get sum based on multiple column group/filter

[ApurvaKhatri]

Hi I have following data

Invoice1	Invoice2	Invoice 3	Invoice 4	Invoice 5	Invoice 6	Repaircost
1	0	0	0	0	0	-5867.69
2	15	15	24			-4329.87
6	999	999				-4067.83
7	26	43				-3833.62
15	6	24				-3415.22
17	43	45				-3409.17
20	21	20				-3172.88
24	7					-3067.57
43	1					-2581.43
997	24					-2565.77
999	997					-2500
	998					-2386.54
	13					-2251.2
	45					-2220.21
	17					-2095
	20					-1840.56
	51					-1650
						-1650
						-1475
						-1428.13
						-1379
						-1367.25
						-1320.69
						-1250
						-1051.91
						-1019.31
						-1000
						-950
						-915.63
						-853.77
						-844.69
						-822.41
						-801.56
						-800
						-785.29
						-750
						-750
						-733.9
						-731.93
						-716.95
						-705.66
						-703.39
						-687.93
						-687.55
						-656.87
						-654.43
						-650
						-650
						-611.76
						-607.62
						-591.69
						-588.3
						-569.74
						-536.26
						-528.6
						-500
						-500
						-500
						-475
						-470.04
						-456.61
						-450
						-433.81
						-415.8
						-390.96
						-374.52
						-369.6
						-350
						-350
						-348.36
						-324.56
						-320.49
						-308.58
						-281.8
						-277.2
						-270.25
						-260
						-260
						-250
						-240
						-234.3
						-234.28
						-230.76
						-225
						-200.3
						-200
						-198.03
						-193.03
						-190.6
						-180
						-178.75
						-175
						-166.32
						-138.6
						-137
						-137
						-116.03
						-109
						-90.72
						-65

Every repaircost is associated with either of the invoices 

e.g -65 is associated with Invoice 1 and value 0.

I need to sum(repaircost) based on values of invoice i.e

0 - sum(repaircost) 

1- sum(repaircost)

......

like invoice 1,invoice 2,invoice 3, invoice 4, invoice 5 and invoice 6 has value 0, so I need to sum(repaircost) of value 0 irrespective of invoices.

How can i acheive this?

Thanks,

Apurva

[v-caliao-msft]

@ApurvaKhatri,

It's hard to understand your requirement based on your description and sample data. We cannot understand the relations between repaircost and invoice.  So please elaborate it, so that we can make further analysis.

Regards,

Charlie Liao

[ApurvaKhatri]

Type1   Cost1                     

0         100

1          400

3          600

5          800

6          200

7          1000

Type2  Cost2

1          500

2          1200

5          500

6          200

Type3    Cost3

5             100

6             100

7             400

Now, I want to sum(cost1) + sum(cost2) + sum(cost3) but i want only 1 column of type. ( I want all the distinct values in a column of types and sum all cost based on types)

Output Table:

Type(combine type 1,type 2, type 3 into one column)      Cost (sum all three costs based on value in column)

0                                                                                           100

1                                                                                            900 (sum((cost 1) + cost2)))

2                                                                                               1200 

3                                                                                               600

5                                                                                              1400 (cost 1 + cost2 + cost3)

6                                                                                                   500  (cost 1 + cost2 + cost3)

7                                                                                               1400 (cost1 + cost3)

Thank you.

[v-caliao-msft · Accepted solution]

@ApurvaKhatri,

You need to rename those column in tables to ensure that all the columns name are the same on each table, and then append those queries in Query Editor.

Then create a new table by using the DAX.

Table = SUMMARIZE(Table1,Table1[Type],"TotalCost",CALCULATE(SUM(Table1[Cost]),ALLEXCEPT(Table1,Table1[Type])))

Regards,

Charlie Liao