Averagex not working as expected

[Anonymous]

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 !! 

[Anonymous · Accepted solution]

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]

@Anonymous , Try a measure like

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

[Anonymous]

@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]

@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]

@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