Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Averagex not working as expected

Hello ,

I have a fact table that contains this two value 

 

i create a measure that return the Qty on hand through the year as below the result of my measure:

 

Now , i want to calculate the average of Qty on hand (previous result) .

 

I create a new measure avg , that call the previous measure like as below : 

 

 

average of Qty on hand = averagex('Fact table',[Qty on hand])

 

As below the result :

 

The result that i get it isn't what i want because i get only that average (238+298)/2  (because i have only two rows on fact table)

 

While  i want to have an average or all the row  ((238*7)+(298*5))/12

 

FYI : I want also that measure work with another context for example if i choose another dimension 

 

Any idea how can i do that ? 

 

Thanks for help !! 

 
Need Help
Need Help DAX
Need Help DAX measure
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create calculated table.

    Date =
CALENDAR(DATE(2019,1,1),DATE(2019,12,31))

    2. Create calculated column.

    left = VALUE( LEFT( RIGHT('Table'[Date Avaliavle],4),2))

    3. Create measure.

    Qty_Month =
IF(
    MAX('Date'[Month])>=1&&MAX('Date'[Month])<8,CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[left]=1)),CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[left]=8)))

    4. Create calculated table.

    Table 2 =
var _table1=SUMMARIZE('Date','Date'[Month],"if",
IF(
    'Date'[Month]>=1&&'Date'[Month]<8,CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[left]=1)),CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[left]=8))))
var _table2=ADDCOLUMNS(_table1,"count",COUNTX(FILTER(_table1,[if]=EARLIER([if])),[Month]))
var _table3=ADDCOLUMNS(_table2,"amount",[if]*[count])
var _table4=SUMMARIZE(_table3,[count],[amount])
return
_table4

    5. Create measure.

    average of Qty on hand =
DIVIDE( SUMX(ALL('Table 2'),[amount]),SUMX(ALL('Table 2'),[count]))

    6. Result:

     

    Best Regards,

    Liu Yang

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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Try a measure like

     

    average of Qty on hand = averagex(values('Fact table'[Month Year]),[Qty on hand])

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      amitchandak thanks for the reply.

      Well your solution doesn't work , in my fact table the granularity is date then i tried that : 

      average of Qty on hand = averagex(values('Fact table'[dateid]),[Qty on hand])


      The result that i get is like the measure that i created before.
      Fyi : as bellow the data that i have on my fact : 

      The measure take only those values and calculate the average  (238+298)/2  

       

      While  i want to have an average or all the row  ((238*7)+(298*5))/12

       

       

      Thanks in advance for your time and help ! 

       



      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        Anonymous , This should happen at month year level for visual

         

         

        average of Qty on hand = averagex(values('Fact table'[Month year]),[Qty on hand])

         

         

        or

         

        Measure 1 
average of Qty on hand day= averagex(values('Fact table'[dateid]),[Qty on hand])

Measure 2 
average of Qty on hand Month = 
averagex(values('Fact table'[Month Year]),[average of Qty on hand day])

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create calculated table.

    Date =
CALENDAR(DATE(2019,1,1),DATE(2019,12,31))

    2. Create calculated column.

    left = VALUE( LEFT( RIGHT('Table'[Date Avaliavle],4),2))

    3. Create measure.

    Qty_Month =
IF(
    MAX('Date'[Month])>=1&&MAX('Date'[Month])<8,CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[left]=1)),CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[left]=8)))

    4. Create calculated table.

    Table 2 =
var _table1=SUMMARIZE('Date','Date'[Month],"if",
IF(
    'Date'[Month]>=1&&'Date'[Month]<8,CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[left]=1)),CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[left]=8))))
var _table2=ADDCOLUMNS(_table1,"count",COUNTX(FILTER(_table1,[if]=EARLIER([if])),[Month]))
var _table3=ADDCOLUMNS(_table2,"amount",[if]*[count])
var _table4=SUMMARIZE(_table3,[count],[amount])
return
_table4

    5. Create measure.

    average of Qty on hand =
DIVIDE( SUMX(ALL('Table 2'),[amount]),SUMX(ALL('Table 2'),[count]))

    6. Result:

     

    Best Regards,

    Liu Yang

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