topic Re: Averagex not working as expected in DAX Commands and Tips

Averagex not working as expected

Anonymous — Tue, 22 Feb 2022 23:56:35 GMT

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

Re: Averagex not working as expected

amitchandak — Wed, 23 Feb 2022 06:05:30 GMT

Anonymous , Try a measure like

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

Re: Averagex not working as expected

Anonymous — Wed, 23 Feb 2022 08:50:14 GMT

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 !

Re: Averagex not working as expected

amitchandak — Wed, 23 Feb 2022 09:13:08 GMT

Anonymous , This should happen at month year level for visual

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

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

Re: Averagex not working as expected

Anonymous — Wed, 23 Feb 2022 09:36:38 GMT

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

Re: Averagex not working as expected

Anonymous — Fri, 25 Feb 2022 05:52:01 GMT

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