Forum Discussion
Calculating average based on different column.
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
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