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 there,
Kindly help to have DAX formula for the below table
each item has different amount either issued or received (+1 or -1) and i need to have average cost of an each item irrespective of date
Date | Item No. | Item Qty | Description | Amount |
31/03/2019 | In house | 1 | In houDe | 6,076,135.41 |
31/01/2019 | In house | 1 | In houDe | 5,532,795.11 |
31/12/2018 | In house | 1 | In houDe | 4,959,654.48 |
31/10/2018 | In house | 1 | In houDe | 4,909,921.49 |
30/11/2018 | In house | 1 | In houDe | 4,900,504.26 |
30/09/2018 | In house | 1 | In houDe | 4,253,778.16 |
28/02/2019 | In house | 1 | In houDe | 3,767,478.03 |
31/07/2018 | In house | 1 | In houDe | 3,519,880.51 |
30/06/2018 | In house | 1 | In houDe | 2,925,793.64 |
24/07/2018 | 161W2100-2 | -1 | MOMPONENT ADDY - MLG | - 2,425,042.25 |
01/07/2018 | 161W2100-2 | 1 | MOMPONENT ADDY - MLG | 2,210,740.01 |
24/07/2018 | 161W2100-2 | -1 | MOMPONENT ADDY - MLG | - 2,210,726.43 |
24/07/2018 | 162W1100-14 | -1 | DHOMKDTKUTADDY | - 2,210,726.43 |
26/02/2019 | B445000-038 | -1 | TOP MOLLEMTOK | - 1,964,821.76 |
31/05/2018 | In house | 1 | In houDe | 1,753,442.25 |
31/10/2018 | 201585003-020 | -1 | LEG AND DKEDDINGD-MLG | - 1,605,675.73 |
30/04/2018 | In house | 1 | In houDe | 1,452,260.74 |
21/05/2018 | 201585003-020 | 1 | LEG AND DKEDDINGD-MLG | 1,279,996.79 |
31/05/2018 | 201585003-020 | -1 | LEG AND DKEDDINGD-MLG | - 1,278,030.38 |
01/03/2019 | 47172-500 | 1 | AMTUATOK - THD | 1,274,525.00 |
30/09/2018 | In house | 1 | In houDe | 1,110,838.92 |
23/10/2018 | 683L250G03 | 1 | KIT -FAN KEVEKDEK PODN 2 KH | 1,108,387.97 |
16/07/2018 | F71AR010000200 | 1 | AIK INLET NODE MOWL | 1,087,393.38 |
26/10/2018 | 7010101H09 | 1 | MAM - MABIN AIK MOMPKEDDOK | 866,205.24 |
20/10/2018 | 7010101H09 | 1 | MAM - MABIN AIK MOMPKEDDOK | 861,315.77 |
31/08/2018 | In house | 1 | In houDe | 858,814.37 |
03/06/2018 | 7010101H09 | 1 | MAM - MABIN AIK MOMPKEDDOK | 833,621.72 |
01/06/2018 | 7010101H09 | 1 | MAM - MABIN AIK MOMPKEDDOK | 823,105.72 |
03/06/2018 | 7010101H09 | 1 | MAM - MABIN AIK MOMPKEDDOK | 812,185.05 |
08/08/2018 | 7010101H09 | 1 | MAM - MABIN AIK MOMPKEDDOK | 809,483.84 |
07/11/2018 | 7010101H09 | 1 | MAM - MABIN AIK MOMPKEDDOK | 803,936.94 |
01/12/2018 | 161W2130-1 | 1 | TKUMK MOMPONENT ADDEMBLY | 792,355.77 |
01/12/2018 | 161W4200-10 | 1 | DTKUT ADDY - DKAG | 792,355.77 |
01/02/2019 | 161W2100-1 | 1 | MOMPONENT ADDY - MLG | 792,355.77 |
01/02/2019 | 161W2100-2 | 1 | MOMPONENT ADDY - MLG | 792,355.77 |
30/04/2018 | In house | 1 | In houDe | 787,462.24 |
01/08/2018 | 7010101H09 | 1 | MAM - MABIN AIK MOMPKEDDOK | 783,795.15 |
28/09/2018 | 7010101H09 | 1 | MAM - MABIN AIK MOMPKEDDOK | 778,905.67 |
31/12/2018 | 7010101H09 | 1 | MAM - MABIN AIK MOMPKEDDOK | 778,905.67 |
18/09/2018 | 752168B | 1 | IDG-INTEGKATED DKIVE GENEKATOK | 737,077.33 |
Thanks in advance
Nizam
Solved! Go to Solution.
Hi @Anonymous
How would you want to take the quantity into consideration to calculate the average? Can you provide an example with one of the items with the expected result? It's not quite clear.
You can try something like this but I'm not sure it's what you're after:
1. Place Table1[Item No.] in a table visual
2. Create this measure and place it on the visual
Measure = AVERAGE(Table1[Amount])
Hi @Anonymous
How would you want to take the quantity into consideration to calculate the average? Can you provide an example with one of the items with the expected result? It's not quite clear.
You can try something like this but I'm not sure it's what you're after:
1. Place Table1[Item No.] in a table visual
2. Create this measure and place it on the visual
Measure = AVERAGE(Table1[Amount])
Hi,
Thanks for the reply
and let me elaborate
Each item (which is repeating in the table) has different Amount
i need average of cost (including positive and negative values) of each item through DAX formula
Thanks in advance for your time
Regards
Nizam
@Anonymous
I'm afraid it's still unclear. Why don't you give me an example with one of the items, showing how it would be calculated and the result . I don't understand what you mean by "(including positive and negative values)"
Hi
Please find below expected result from above table through DAX formula
Item | Average of Amount |
161W2100-1 | 792355.77 |
161W2100-2 | -408168.23 |
161W2130-1 | 792355.77 |
161W4200-10 | 792355.77 |
162W1100-14 | -2210726.43 |
201585003-020 | -534569.77 |
47172-500 | 1274525.00 |
683L250G03 | 1108387.97 |
7010101H09 | 807235.87 |
752168B | 721710.80 |
B445000-038 | -1964821.76 |
F71AR010000200 | 1087393.38 |
In house | 3343482.83 |
Thanks and regards
Nizam
The solution I provided earlier should produce exactly that result. Doesn't it?
Yes, it is
Thanks a lot for the solution and your time
Regards
Nizam
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 |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |