Forum Discussion
Calculating average based on different column.
Hello Everyone,
I hope to get some help on this DAX or any idea on how to solve this issue. Below is just an example of the data.
I created a column that would do some average
when using this calculation it gives 19 instead of 18 value. so when we add the avg coulmn= 89/5=18 which is the correct value but in the average calculated column I see 19. I am not sure what the reason could be. for other customers the average is correct
If anyone could help me that would be great.
| Customer | salesperson | group | accountnumber | avg | avg calculatedcolumn |
| A | B | 001 | 23 | 13 | 19 |
| A | B | 001 | 23 | 16 | 19 |
| A | B | 001 | 23 | 17 | 19 |
| A | B | 001 | 23 | 21 | 19 |
| A | B | 001 | 23 | 22 | 19 |
var c = table_name[Customer] var s = table_name[salesperson] var g = table_name[group] var an = table_name[account number] return averagex ( filter( summarize( table_name, table_name[Customer], table_name[salesperson], table_name[group], table_name[account number], table_name[avg] ), table_name[Customer] = c && table_name[salesperson] = s && table_name[group] = g && table_name[account number] = an ), table_name[avg] )let me know if it works for you .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠Customer Salesperson Group account number avg Date
7 Replies
i just removed the year in allexcept .
im not sure if this is what you want . but it returns the desired number .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠Hello Daniel29195,
Thank you for the reply.
I did removed the year but still it is showing me 19 is there anything that I should try. I tried removing each column and running it but no luckHi Nepal101 ,
I have a test by code as below.
My Code = CALCULATE ( AVERAGEX ( 'D-with dates', 'D-with dates'[Avg] ), ALLEXCEPT ( 'D-with dates', 'D-with dates'[Customer], 'D-with dates'[accountnumber], 'D-with dates'[group] ) )I create a measure and a calculated column to have a test.
I get the same result as Daniel29195, please share a sample with us, this will make it eaiser for us the find the solution.
You also can download my attatchment to compare my data model with yours.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Daniel29195 and Anonymous
Thank you for the reply I don't think I have an option to attach the pbix file. Please let me know how can I attach the PBIX file.
so when I looked at the data again on the data view there were numbers duplicated due to the dates so that was the reason I was getting 19. as there was 39 count of rows and 733 sum = 18.79 so in this case is there a way to calculate average by creating distinct count and distinct sum so the value could be 5 distinct count rows and 89 sum. I hope the below example can be helpful. Please let me know how can I achieve this. The data is such that I can't remove the duplicates I need that for other reports. Thank you again for your time and for looking into this.Customer Salesperson Group account number avg Date A B 001 02 13 01/22/2023 A B 001 02 16 2/22/2023 A B 001 02 17 4/22/2023 A B 001 02 21 8/24/2023 A B 001 02 22 9/22/2023 A B 001 02 13 01/22/2023 A B 001 02 16 2/22/2023 A B 001 02 17 4/22/2023 A B 001 02 21 8/24/2023 A B 001 02 22 9/22/2023 A B 001 02 13 01/22/2023 A B 001 02 16 2/22/2023 A B 001 02 17 4/22/2023 A B 001 02 21 8/24/2023 var c = table_name[Customer] var s = table_name[salesperson] var g = table_name[group] var an = table_name[account number] return averagex ( filter( summarize( table_name, table_name[Customer], table_name[salesperson], table_name[group], table_name[account number], table_name[avg] ), table_name[Customer] = c && table_name[salesperson] = s && table_name[group] = g && table_name[account number] = an ), table_name[avg] )let me know if it works for you .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠Customer Salesperson Group account number avg Date Thank you so much this worked Daniel29195
