AVERAGEX not producing an average???

[masplin]

I must have misunderstood AVERAGEX as the answer isn't the average of the rows that i think are being fed in?

=AVERAGEX(Transactions,                     CALCULATE(
                                           DISTINCTCOUNT(Transactions[EcrID]),
                                           FILTER(
                                                        Transactions,
                                                         [StoreDayQuarterID]=EARLIER([StoreDayquarterID])
                                                        )
                                           )
                      )

where StoreDayQuarterID represents a unique identifier of each 15 minute slot for each store. ErcID is the identifier of the till in use. So the idea being to calculate in each 15 minute window how many different tills were used and then average the lot.

However if i build a pivot table with StoreDayQuarterID as the rows and Distinctcount of ErcID as the values and this measure also as a value I see this table(cant work out how to insert a table!)

[TABLE="width: 370"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]StoreDayQtrID[/TD]
[TD]Till usage per Qtr per store[/TD]
[TD]Distinct Count of EcrID[/TD]
[/TR]
[TR]
[TD]000642125.4375[/TD]
[TD] 4.00[/TD]
[TD] 4.00[/TD]
[/TR]
[TR]
[TD]000642129.4375[/TD]
[TD] 2.00[/TD]
[TD] 2.00[/TD]
[/TR]
[TR]
[TD]000642130.4375[/TD]
[TD] 5.00[/TD]
[TD] 5.00[/TD]
[/TR]
[TR]
[TD]000642131.4375[/TD]
[TD] 3.00[/TD]
[TD] 3.00[/TD]
[/TR]
[TR]
[TD]000642132.4375[/TD]
[TD] 5.00[/TD]
[TD] 5.00[/TD]
[/TR]
[TR]
[TD]000642135.4375[/TD]
[TD] 4.00[/TD]
[TD] 4.00[/TD]
[/TR]
[TR]
[TD]000642136.4375[/TD]
[TD] 4.00[/TD]
[TD] 4.00[/TD]
[/TR]
[TR]
[TD]000642137.4375[/TD]
[TD] 5.00[/TD]
[TD] 5.00[/TD]
[/TR]
[TR]
[TD]000642138.4375[/TD]
[TD] 3.00[/TD]
[TD] 3.00[/TD]
[/TR]
[TR]
[TD]000642139.4375[/TD]
[TD] 2.00[/TD]
[TD] 2.00[/TD]
[/TR]
[TR]
[TD]000642142.4375[/TD]
[TD] 2.00[/TD]
[TD] 2.00[/TD]
[/TR]
[TR]
[TD]000642143.4375[/TD]
[TD] 4.00[/TD]
[TD] 4.00[/TD]
[/TR]
[TR]
[TD]000642144.4375[/TD]
[TD] 4.00[/TD]
[TD] 4.00[/TD]
[/TR]
[TR]
[TD]000642145.4375[/TD]
[TD] 4.00[/TD]
[TD] 4.00[/TD]
[/TR]
[TR]
[TD]000642146.4375[/TD]
[TD] 3.00[/TD]
[TD] 3.00[/TD]
[/TR]
[TR]
[TD]000642150.4375[/TD]
[TD] 5.00[/TD]
[TD] 5.00[/TD]
[/TR]
[TR]
[TD]000642151.4375[/TD]
[TD] 3.00[/TD]
[TD] 3.00[/TD]
[/TR]
[TR]
[TD]000642152.4375[/TD]
[TD] 3.00[/TD]
[TD] 3.00[/TD]
[/TR]
[TR]
[TD]000642153.4375[/TD]
[TD] 3.00[/TD]
[TD] 3.00[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD] 3.79[/TD]
[TD] 7[/TD]
[/TR]
</tbody>[/TABLE]


As you can see on each line the measure has correctly calculated the number of tills (column 2) . However the average of these rows is 3.58 NOT 3.79. What have I done wrong as cannot think of any way to get to 3.79.

Thanks for any advice

Mike

 

[Ozeroth]

Hi Mike,

From your description, the [Till usage per Qtr per store] measure calculates an average over all rows of Transactions (in the filter context).

However in the pivot table, presumably each StoreDayQtrID corresponds to multiple rows of transactions(?)

When the Grand Total is calculated, the [Till usage per Qtr per store] measure corresponding to each StoreDayQtrID will effectively be weighted by the number of rows of Transactions that have that StoreDayQtrID.

To get the 3.58 result, the measure would have to be defined differently so that it averages over StoreDayQtrIDs rather than every row of the Transactions table (assuming that's what you want).
Something like AVERAGEX( VALUES(Transactions[StoreDayQtrID]), ... )

Owen

 

[masplin]

Your the best. Spot on thanks

 

[masplin]

This is an extremely slow calculation and actually it isn't the crossjoin/summarize function we are discussing on the other thread making my whole model slow. Would this be faster using the same approach and a SUMMARIZE function to reduce the size of the table it is trying to do the distinct count on? I assume this is taking the whole table and looping through it for each combination of StoreDayQtrID. Takes hours!!!!

 

[masplin]

This seems to produce the same answer so would you expect it to be quicker or makes no difference?

=AVERAGEX ( SUMMARIZE (
Transactions,
Transactions[StoreDayQuarterID]
),
CALCULATE(
DISTINCTCOUNT(Transactions[EcrID])
)
)

Real learning experience for me as never used these AGGX functions before so be handy to know the most efficient way to write them.

Thanks
Mike

 

[Ozeroth]

Yes this should be quicker.
The earlier version iterated twice over the Transactions table: once for AVERAGEX and once for FILTER.
The new version only iterates over a table of distinct [StoreDayQuarterID] (fewer rows than Transactions) then does a DISTINCTCOUNT for each of those.

 

[masplin]

I just realized my SUMMARIZE version of this has gone back to the same issue as before where it is averaging over all transaction rows. not obvious to me what I need to do so the averageX only calculates for each value of the hour:quarter

As you can see the total should be a round number when I have a single day/store/hour:quarter i.e. 2nd row total should be 3

[TABLE="width: 677"]
<tbody>[TR]
[TD]H:Q[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]2.6[/TD]
[/TR]
[TR]
[TD]7:0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2.0[/TD]
[/TR]
[TR]
[TD]7:15[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2.0[/TD]
[/TR]
[TR]
[TD]7:30[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]3.3[/TD]
[/TR]
[TR]
[TD]7:45[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]1.0[/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]3.7[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ozeroth]

Sorry a little hard to interpret the table.
Could you restate the DAX for the measure you are using, and could you post a link to a file with current vs expected output?

 

[masplin]

Sorry being completely dim and thought the 27 was the day but it was the week so was averaging over 5 days hence not producing round numbers. Wasting your time apologise

Mike

 

[Ozeroth]

No worries, that's good if it's behaving as expected