Forum Discussion
Get sum based on multiple column group/filter
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
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
3 Replies
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
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.
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
