AVERAGEX iterating over one table with calculation on another

[dicken]

Sorry for a poor description, essentially in the scenario the granularity of an average was changed by iterating over the linked date table;

So to start you have ;

 Average M:=AVERAGEX(fTransactions,[Total Revenue Iterate Over Fact])

Fine, but do change the granularity this then becomes

AVERAGEX(VALUES(dDate[Year Month]),[Total Revenue Iterate Over Fact])

But the total revenue measure is ;

    Total Revenue Iterate Over Fact:=SUMX(fTransactions,fTransactions[UnitsSold]*RELATED(dProduct[RetailPrice]))

So my question , at last is how can I incorrporate the abvoe code into the AVERAGEX formula iterating over the date table and not use the measure,

I've tried various options none of which work,

  AVERAGEX( VALUES(dDate[Year Month]),CALCULATE(SUM(fTransactions[UnitsSold])*dProduct[RetailPrice]))

The clsoeset I've got is

  AVERAGEX( VALUES(dDate[Year Month]),SUMX(fTransactions,fTransactions[UnitsSold]*RELATED(dProduct[RetailPrice])))

Which I knew wouldn't work but at least gave an answer, it must be possible or it wouldn't work with the measure?
What am I overlooking , getting wrong?

Richard.

 

[Matt Allington]

I haven’t seen the model, but my guess is this.

=
AVERAGEX (
    VALUES ( dDate[Year Month] ),
    SUMX (
        VALUES ( dProduct[RetailPrice] ),
        CALCULATE (
            SUM ( fTransactions[UnitsSold] )
        ) * dProduct[RetailPrice]
    )
)

 

[dicken]

I haven’t seen the model, but my guess is this.

=
AVERAGEX (
    VALUES ( dDate[Year Month] ),
    SUMX (
        VALUES ( dProduct[RetailPrice] ),
        CALCULATE (
            SUM ( fTransactions[UnitsSold] )
        ) * dProduct[RetailPrice]
    )
)

Well it was a very good guess,
So the AVERAGEX iterates over the unique Date Year 'table',
SUMX is iterating over the product table; I think CALCULATE is enables the entire Data mode to be seen and causes the
filter context for each 'row' of units sold * price ?

In my simplistic way I did try

Copy of Using Formula :=
AVERAGEX (
    VALUES ( dDate[Year Month] ),
    SUMX (
        fTransactions,
        fTransactions[UnitsSold] * RELATED ( dProduct[RetailPrice] )
    )
)

But this just returns the total, any further information or reading suggestions alway welcome.

Richard.

 

[Matt Allington]

It's a long journey, but you are well underway.

Yes, the CALCULATE in my formula forces context transition for each row in the iteration. Here is pseudo code

total = 0
result for this row = 0

for r = 1 to <number of rows in table>
   Filter the dimension table for row = r
   propagate the filters from the dimension table to the fact table (using CALCULATE)
   result for this row = sum(fact[column])  'of the now filtered fact table
   total = total + result for this row
next r

return total

If you don't use calculate, you get the following pseudo code

total = 0
result for this row = 0

for r = 1 to <number of rows in table>
   don't filter the dimension table for row = r
   don't propagate the filters from the dimension table to the fact table, because you didn't specify this behaviour
   result for this row = sum(fact[column])  'of the entire unfiltered fact table
   total = total + result for this row 'which is the entire table for each row in the iteration
next r

return total (which is <number of row in table> * SUM(unfiltered fact table)

The issues with your formula are

1. You are missing the CALCUALTE to force context transition.
2. You are iterating the fact table when it is better to iterate the dimension table (as a general rule, aim for the smallest table).

This should work, but mine is still more efficient.

Copy of Using Formula :=
AVERAGEX (
    VALUES ( dDate[Year Month] ),
    CALCULATE(SUMX (
        fTransactions,
        fTransactions[UnitsSold] * RELATED ( dProduct[RetailPrice] )
    ))
)

Technically, in my formula I am iterating the Price column (converted to table by VALUES), not the product table. Often, but not always, this is a smaller (virtual) table than the dimension table itself.

Further reading

When to Use SUMX() vs SUM() in DAX

Learn the main insights of SUMX() vs SUM() in DAX with examples, so you can decide which formula works best to get excellent reports.?

exceleratorbi.com.au

 

[dicken]

It's a long journey, but you are well underway.

Yes, the CALCULATE in my formula forces context transition for each row in the iteration. Here is pseudo code

total = 0
result for this row = 0

for r = 1 to <number of rows in table>
   Filter the dimension table for row = r
   propagate the filters from the dimension table to the fact table (using CALCULATE)
   result for this row = sum(fact[column])  'of the now filtered fact table
   total = total + result for this row
next r

return total

If you don't use calculate, you get the following pseudo code

total = 0
result for this row = 0

for r = 1 to <number of rows in table>
   don't filter the dimension table for row = r
   don't propagate the filters from the dimension table to the fact table, because you didn't specify this behaviour
   result for this row = sum(fact[column])  'of the entire unfiltered fact table
   total = total + result for this row 'which is the entire table for each row in the iteration
next r

return total (which is <number of row in table> * SUM(unfiltered fact table)

The issues with your formula are

1. You are missing the CALCUALTE to force context transition.
2. You are iterating the fact table when it is better to iterate the dimension table (as a general rule, aim for the smallest table).

This should work, but mine is still more efficient.

Copy of Using Formula :=
AVERAGEX (
    VALUES ( dDate[Year Month] ),
    CALCULATE(SUMX (
        fTransactions,
        fTransactions[UnitsSold] * RELATED ( dProduct[RetailPrice] )
    ))
)

Technically, in my formula I am iterating the Price column (converted to table by VALUES), not the product table. Often, but not always, this is a smaller (virtual) table than the dimension table itself.

Further reading

When to Use SUMX() vs SUM() in DAX

Learn the main insights of SUMX() vs SUM() in DAX with examples, so you can decide which formula works best to get excellent reports.?

exceleratorbi.com.au

Thanks once again,

RD

 

[dicken]

I haven’t seen the model, but my guess is this.

=
AVERAGEX (
    VALUES ( dDate[Year Month] ),
    SUMX (
        VALUES ( dProduct[RetailPrice] ),
        CALCULATE (
            SUM ( fTransactions[UnitsSold] )
        ) * dProduct[RetailPrice]
    )
)

I know this was a while ago but am I correct in thinking the CALCULATE, is needed because if I were using a measure there would be an implicit calculate already there?

Richard