Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 beli...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    Try

     

    Avg_Unit_Rate =
IF (
    TableName[Touch Point] = "Main",
    CALCULATE (
        AVERAGE ( TableName[Avg Unit Rate] ),
        ALLEXCEPT ( TableName, TableName[ID Number], TableName[Doc Number] )
    )
)
MFelix's avatar
MFelix
Super User
8 years ago

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 NumberDoc NumberUnit RateQtyAvg Unit RateQty Consumed
123AA1124517.557
123AA1231217.557
123AA211451145
124BE226122612
124BD112651265
124BD3122111.5342
124BD31132111.5342
125DD1251130.523
125DD421212121
125DD1361230.523
126DD136153615
126DD421122112

 

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

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