AVERAGEX help needed

[Anonymous]

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

[MFelix]

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

[Anonymous]

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.

[Zubair_Muhammad · Accepted 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] )
    )
)

[Anonymous]

Hi @Zubair_Muhammad - thanks a bunch for your response. Would this be a measure or a calculated column ?

[Zubair_Muhammad]

hi

It is a calculated column

[Anonymous]

Nice!!! I will try and let you know. Thanks again for your help.

[Anonymous]

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.