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.

Anonymous's avatar
Anonymous
Not applicable
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.

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

Hi Anonymous ,

Thank you,

But why join all code not work :

safety for group = 
var safety = [avg] * [standard_deviation]
return AVERAGEX(VALUES('item_master'[item]),safety)

It's only work when :
safety = [avg] * [standard_deviation]
safety for group = AVERAGEX(VALUES('item_master'[item]),[safety])
 

If there's any simple references how the logic/architecture of dax engine work, please let me know

Sincerely,
Oviedityanto