Forum Discussion

cst_bi's avatar
cst_bi
Icon for Helper I rankHelper I
2 years ago
Solved

DAX to average a measure that calculated from multiple table

Hi Experts,   I need to calculate the group average from a measure which it's calculated based on another table Here is how it should work    1. Create an average measure from purchase table it...
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi cst_bi ,

     

    According to your statement, I think your issue should be caused by that you remove [item] from your table visual. So [safety] measure will be summarized based on data in your table visual.

    I could reproduce your issue in my sample.

    Data model:

    avg = AVERAGE('purchase table'[qty])/3
    standard_deviation = STDEVX.S('issue table','issue table'[qty])
    safety = [avg] * [standard_deviation]

    Here I suggest you to create a new measure for this visual.

    safety for group = 
AVERAGEX(VALUES('item_master table'[item]),[safety])

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

SachinNandanwar's avatar
SachinNandanwar
Icon for Impactful Individual rankImpactful Individual
2 years ago

Rico has already answered your question.

Just wanted to add to Rico's solution regarding the calculation of average for  purchase table , that if the no of items as dynamic you can refine the meaure to this 

avg = AVERAGE('purchase table'[qty])/COUNTROWS(SUMMARIZE('purchase table','purchase table'[item],'purchase table'[date].[Date]))