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

item date qty
a 1/1/2024 5
a 2/1/2024 5
a 3/1/2024 2
b 1/1/2024 3
b 2/1/2024 4
b 3/1/2024 8
c 1/1/2024 2
c 2/1/2024 2
c 3/1/2024 1

 

Calculate avg per item from purchase table :

avg a = avg(qty)/3 = 1.333333333
avg b = avg(qty)/3 = 1.666666667
avg c = avg(qty)/3 = 0.555555556

 

2. Create an average measure from issue table

item date qty
a 1/1/2024 3
a 2/1/2024 4
a 3/1/2024 1
b 1/1/2024 1
b 2/1/2024 2
b 3/1/2024 2
c 1/1/2024 3
c 2/1/2024 1
c 3/1/2024 1

 

Calculate standard deviation per item :

standard_deviation a = stdev.s(qty) = 1.527525232
standard_deviation b = stdev.s(qty) = 0.577350269
standard_deviation c = stdev.s(qty) = 1.154700538

 

3. Calculate safety column measure from measure

safety a  = avg * standard_deviation = 2.036700309
safety b  = avg * standard_deviation = 0.962250449
safety c  = avg * standard_deviation = 0.641500299

 

So, the output table would be :

item avg standard_deviation safety
a 1.333333333 1.527525232 2.036700309
b 1.666666667 0.577350269 0.962250449
c 0.555555556 1.154700538 0.641500299

 

4. Calculate average of safety, group by group column from item_master table :

item group
a g1
b g1
c g2

 

The final output table should be :

group average of safety
g1 1.499475379
g2 0.641500299

 

Here is my DAX  to calculate measure :

_avg = AVERAGE(purchase[qty])/3
_std_dev = STDEV.S(issue[qty])
_safety = [_avg] * [_std_dev]
 
But when calculate using group column, it show incorrect value
group _safety
g1 1.753567792
g2 0.641500299

 

Please give us an advice for the right value

 

Thank you for your help

Sincerely,
Oviedityanto

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.

3 Replies

  • 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]))

     

  • 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