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
Icon for Super User rankSuper 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

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
    Icon for Community Champion rankCommunity 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 ?

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago
        hi

        It is a calculated column