Forum Discussion
AVERAGEX help needed
Hello Experts, looking for some guidance or help in calculating Average Unit rate values based on 2 conditions (ID num, Doc Num). If I had to do it in excel, I can simply use AVERAGEIFS, which I believe can be done using AVERAGEX in DAX. Though I tried hard, I am unable to get the correct output. I would appreciate any help on this. Below is the dataset. I have appended 2 queries in one table with a touchpoint field. I need avg unit rate, and sum of qty for Main lines, from Numbers.
| Touch Point | ID Number | Doc Number | Unit Rate | Qty | Avg Unit Rate | Qty Consumed |
| Main | 123 | AA1 | 17.5 | 57 | ||
| Main | 123 | AA1 | 17.5 | 57 | ||
| Main | 123 | AA2 | 11 | 45 | ||
| Main | 124 | BE2 | 26 | 12 | ||
| Main | 124 | BD1 | 12 | 65 | ||
| Main | 124 | BD3 | 11.5 | 342 | ||
| Main | 124 | BD3 | 11.5 | 342 | ||
| Main | 125 | DD1 | 30.5 | 23 | ||
| Main | 125 | DD4 | 21 | 21 | ||
| Main | 125 | DD1 | 30.5 | 23 | ||
| Main | 126 | DD1 | 36 | 15 | ||
| Main | 126 | DD4 | 21 | 12 | ||
| Numbers | 123 | AA1 | 12 | 45 | ||
| Numbers | 123 | AA1 | 23 | 12 | ||
| Numbers | 123 | AA2 | 11 | 45 | ||
| Numbers | 124 | BE2 | 26 | 12 | ||
| Numbers | 124 | BD1 | 12 | 65 | ||
| Numbers | 124 | BD3 | 12 | 21 | ||
| Numbers | 124 | BD3 | 11 | 321 | ||
| Numbers | 125 | DD1 | 25 | 11 | ||
| Numbers | 125 | DD4 | 21 | 21 | ||
| Numbers | 125 | DD1 | 36 | 12 | ||
| Numbers | 126 | DD1 | 36 | 15 | ||
| Numbers | 126 | DD4 | 21 | 12 |
Anonymous
Try
Avg_Unit_Rate = IF ( TableName[Touch Point] = "Main", CALCULATE ( AVERAGE ( TableName[Avg Unit Rate] ), ALLEXCEPT ( TableName, TableName[ID Number], TableName[Doc Number] ) ) )
7 Replies
Hi Anonymous,
Just ti understand a little better the information to what I can see from the data set the ID number and Doc Number is common with Main lines and Number lines.
Why don't you just make a single merged table all the columns setup would look like this:
ID Number Doc Number Unit Rate Qty Avg Unit Rate Qty Consumed 123 AA1 12 45 17.5 57 123 AA1 23 12 17.5 57 123 AA2 11 45 11 45 124 BE2 26 12 26 12 124 BD1 12 65 12 65 124 BD3 12 21 11.5 342 124 BD3 11 321 11.5 342 125 DD1 25 11 30.5 23 125 DD4 21 21 21 21 125 DD1 36 12 30.5 23 126 DD1 36 15 36 15 126 DD4 21 12 21 12 Or does this not make sense for your data?
Can you please also tell what is the final result you need because based on the dataset you could just do AVERAGE because the measures are context based so using it with different levels of information will make the calculation accordingly.
AVERAGEX is used to calculate the average a table using line by line calculation example, you wanted to make the sum of all the Av Unit rate within ID number and of that make the average.
But if you can add additional expectations I can help you better.
Regards,
MFelix
hi MFelix - thanks for your quick response, I appreciate it. I have 2 tables, Numbers and Main. In main table, I just have ID & Doc number, and in Numbers table, I have ID, Doc #, Unit Rate, Qty. Since I have a date column, and I need to relate it to a calendar table, I merged these 2 tables into one with a touchpoint column. Now, I need to pull the Avg Unit Rate and Total Qty consumed for each of the ID & Doc combination, against ID & Doc of Main lines.
Please let me know if its not clear. thanks again.
Also, I need these 2 to be on as calculated columns instead of measures, as I have a few more further columns being calculated depended on the unit rate and qty consumed.
AVERAGEX is used to calculate the average a table using line by line calculation example, you wanted to make the sum of all the Av Unit rate within ID number and of that make the average.
that is exactly what I need.
Anonymous
Try
Avg_Unit_Rate = IF ( TableName[Touch Point] = "Main", CALCULATE ( AVERAGE ( TableName[Avg Unit Rate] ), ALLEXCEPT ( TableName, TableName[ID Number], TableName[Doc Number] ) ) )
