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.
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 |
Solved! Go to Solution.
@Anonymous
Try
Avg_Unit_Rate = IF ( TableName[Touch Point] = "Main", CALCULATE ( AVERAGE ( TableName[Avg Unit Rate] ), ALLEXCEPT ( TableName, TableName[ID Number], TableName[Doc Number] ) ) )
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsAlso, 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] ) ) )
Hi @Zubair_Muhammad - thanks a bunch for your response. Would this be a measure or a calculated column ?
Nice!!! I will try and let you know. Thanks again for your help.
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.
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |