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

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