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 calcu...
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

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

      amitchandak 

      But what if i want to work for all the level , i need to create all measure for different level ?

      i will have a lot of measure ...... There are any solution to optimize that ? 

      And also what if i have another dimension in my visual i think the average will only for dimension date and not another dimension 

      Thanks