Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |