AVERAGEX using CALCULATE or FILTER, or ALL and ALLEXCEPT.

[jsnow123]

Firstly, I apologise - I am new to Powerpivot and have tried for a couple of hours to wrap my head around this DAX problem and I cannot afford to waste any more time getting gradually more and more confused. I desperately want somebody to give me a leg-up.

In excel. I am presenting a tables containing data A, B, C... X, Y in a table with slicers on "Type" and "Period". In the same data I am trying to create a permanent measure average of 123, for all the periods selected, without "123" selected in a slicer.

I've got as far this, but cannot figure out how to Average123 across the periods selected, so I'm getting the average across all periods.

Av123:=AVERAGEX(FILTER(ALL(MyTable),[Type]="123"),[Value])​

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Value[/TD]
[TD]Period[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0.1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]10[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]100[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0.1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]100[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]0.1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]10[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]100[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]



A quick solution to this would be great, then I can trace it back to try to understand how it all works. I am now just too confused to understand what a FILTER, CALCULATE, AVERAGEX, measures, tables

Many thanks

 

[theBardd]

Try This

CALCULATE( AVERAGEX ( FILTER( MyTable, MyTable[Type] = "123" ), [Sum of Values] ) )

 

[jsnow123]

thanks for your response, but I'm afraid that doesn't work. (note, [Sum of Values] was used in place of [Sum of Value])

Av123:=CALCULATE(AVERAGEX(FILTER(MyTable,MyTable[Type]="123"),[Sum of Value]))

1) it doesn't compute a value for Av123 unless the slicer "123" is selected
2) when "123" is selected by slicer, it doesn't compute the total average per period. I have forgot to mention, that each"Period" has multiple rows (Times, below).


[TABLE="width: 362"]
<tbody>[TR]
[TD]Period[/TD]
[TD]123[/TD]
[TD]Av123[/TD]
[TD](DESIRED Av123)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00:00:00[/TD]
[TD="align: right"]6.51[/TD]
[TD="align: right"]6.51[/TD]
[TD="align: right"]6.57[/TD]
[/TR]
[TR]
[TD]00:00:30[/TD]
[TD="align: right"]6.57[/TD]
[TD="align: right"]6.57[/TD]
[TD="align: right"]6.57[/TD]
[/TR]
[TR]
[TD]00:01:00[/TD]
[TD="align: right"]6.60[/TD]
[TD="align: right"]6.60[/TD]
[TD="align: right"]6.57[/TD]
[/TR]
[TR]
[TD]00:01:30[/TD]
[TD="align: right"]6.59[/TD]
[TD="align: right"]6.59[/TD]
[TD="align: right"]6.57[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]00:00:00[/TD]
[TD="align: right"]8.38[/TD]
[TD="align: right"]8.38[/TD]
[TD="align: right"]8.47[/TD]
[/TR]
[TR]
[TD]00:00:30[/TD]
[TD="align: right"]8.49[/TD]
[TD="align: right"]8.49[/TD]
[TD="align: right"]8.47[/TD]
[/TR]
[TR]
[TD]00:01:00[/TD]
[TD="align: right"]8.49[/TD]
[TD="align: right"]8.49[/TD]
[TD="align: right"]8.47[/TD]
[/TR]
[TR]
[TD]00:01:30[/TD]
[TD="align: right"]8.51[/TD]
[TD="align: right"]8.51[/TD]
[TD="align: right"]8.47[/TD]
[/TR]
</tbody>[/TABLE]

 

[theBardd]

Is this what you mean

=CALCULATE(
    AVERAGEX ( 
        FILTER( MyTable, MyTable[Type] = "123" ), 
        [Sum of Value] ) , 
        ALL( MyTable[Type]
       )
)

 

[jsnow123]

Almost... that now gives an average irrespective of whether "123" is selected by the slicer, but has doesn't provide an average for all Times in the Period...

Am I fundamentally doing something wrong? Am I explaining it correctly?

[TABLE="width: 574"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][TABLE="width: 362"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Period[/TD]
[TD]123[/TD]
[TD]Av123[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10.14[/TD]
[TD]10.14[/TD]
[/TR]
[TR]
[TD]00:00:00[/TD]
[TD]10.01[/TD]
[TD]10.01[/TD]
[/TR]
[TR]
[TD]00:00:30[/TD]
[TD]10.13[/TD]
[TD]10.13[/TD]
[/TR]
[TR]
[TD]00:01:00[/TD]
[TD]10.20[/TD]
[TD]10.20[/TD]
[/TR]
[TR]
[TD]00:01:30[/TD]
[TD]10.21[/TD]
[TD]10.21[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10.33[/TD]
[TD]10.33[/TD]
[/TR]
[TR]
[TD]00:00:00[/TD]
[TD]10.32[/TD]
[TD]10.32[/TD]
[/TR]
[TR]
[TD]00:00:30[/TD]
[TD]10.34[/TD]
[TD]10.34[/TD]
[/TR]
[TR]
[TD]00:01:00[/TD]
[TD]10.33[/TD]
[TD]10.33[/TD]
[/TR]
[TR]
[TD]00:01:30[/TD]
[TD]10.33[/TD]
[TD]10.33[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[theBardd]

Let me try and understand this.

If 123 is selected in the slicer, you want the average of 123 within that period in each row?

If 123 is not selected in the slicer, what do you want to see?

Can you also show some actual data, not just the ave123 or whatever, the actual columns and values including non-123 values, that you have.

 

[jsnow123]

Thanks for sticking in there. Let me try and explain it properly.

I'm using slicers to effectively jump between different periods (of time) and present different types of measurement data across the period. I have slicers to also look at different types of data during these periods, but I want to manipulate different types of data differently: e.g. Value, Max(Value), Average(Value). I used the "123" notation to highlight one manipulation option.

I want to use DAX to create a measure of Average Value of Type "123" <type="123">during the sliced period. <time=sliced period="">

Eventually, I don't actually want to see (slice) data type "123": I want to pass it on to another calculation. I'm selecting the slicer "123" to verify the calculation of Average123.

I can post attach a spreadsheet showing real data, in approx 12 hours from now.

I really appreciate your help. I hope it will further my understanding of DAX...</time=sliced></type="123">