Average from multiple levels?

[seware74]

Given the attached PP model. I am trying to accomplish several things:

1) Compute Contribution Margin for each order. This was accomplished by adding the following calculated columns to the OrderMaster table:

Revenues = SUMX(RELATEDTABLE('SalesDetail'),'SalesDetail'[ShippedSaleAmount])
COGS = SUMX(RELATEDTABLE('SalesDetail'), 'SalesDetail'[COGSAmount)
Ship Cost = SUMX(RELATEDTABLE('Order Shipping'),'Order Shipping'[ShipCost])
Order Contribution Margin= IF([Revenues]-[COGS]-[Ship Cost] <> 0, [Revenues]-[COGS]-[Ship Cost], BLANK())

2) Create a measure that represents average contribution per order. This was accomplished by adding the following calculated measure to the OrderMaster:

Avg Order Contribution Margin:= AVERAGE([Order Contribution Margin])

3) Create a measure that represents average contribution per customer. I can sum at the customer level using a calculated column:

CUSTOMER CONTRIBUTION MARGIN = SUMX(RELATEDTABLE('OrderMaster'),'OrderMaster'[Order Contribution Margin])

but an average measure, as in

:=AVERAGE[CUSTOMER CONTRIBUTION MARGIN]

does not allow me to use the fiscal date table as a dimension of this measure. (fiscal date table is linked to order master). How do create a "average" at the customer level that can be sliced by the fiscal table dimensions.
...In over my head with PP, despite much time reading tutorials and such.

The answer to this question is my ultimate need but if someone could pair that with an explanation (teach me to fish) I would be indebted (and so would my job!)

 

[GDRIII]

It might slow your model down a bit but, adding a few columns and using RELATED to bring the Customer info you want into OrderMaster might do the trick.

Scottsen will probably have a better way...

 

[seware74]

@GDRII, I have account number at the OrderMaster level but I can't figure how to average across account number and not order level (when in the ordermaster table).

I'm missing some fundamental understanding of PP and DAX... funny since I can write SQL in my sleep.

 

[GDRIII]

What I was thinking was that if you added a calculated column to OrderMaster called Customer and used RELATED to bring the Customer Name from your Customer table into the OrderMaster table you could slice by date since you have a date column in that table but, I like to bludgeoning things into working or at least seemingly working.

Have you tried AVERAGEX?

 

[seware74]

Have tried AVERAGEX but failed. I can't figure out how to calculate the average along a different group than the table the measure is in. I want to calculate the total contribution margin per customer and then average that overall. I can accomplish this at the customer table level but that measure doesn't allow slicing by time.

 

[seware74]

I think maybe this is it... can anyone see any fallacy here:

At the customer level a measure:

Avg Customer Contribution = AVERAGEX(Customer,SUMX(RELATEDTABLE(OrderMaster),OrderMaster[Order Contribution Margin])

This still complains 'relationships may be needed' but appears to be correct across the time dimension

 

[scottsen]

3) Create a measure that represents average contribution per customer. I can sum at the customer level using a calculated column:

CUSTOMER CONTRIBUTION MARGIN = SUMX(RELATEDTABLE('OrderMaster'),'OrderMaster'[Order Contribution Margin])

I assume you meant Total contribution margin? (cuz you are getting the sum here )

This measure should be: [Total Margin] := SUM(OrderMaster'[Order Contribution Margin])
which will do the same thing, but perform better. Avoid X functions whenever possible.

The 'Order Shipping' table looks a bit weird to me, like the direction might be wrong or something? I could be nuts there.

Anyway, your instincts are correct on the average margin per customer:
=AVERAGEX(Customer, [Total Margin])
seems reasonable.

 

[seware74]

appreciate your help folks. pointed me in the right direction.


I played with this all weekend using everyone's suggestion and this was the only way I could get what I wanted. At the customer level a measure:

Avg Total Customer Contribution = AVERAGEX(Customer, SUMX(RELATEDTABLE(OrderMaster),OrderMaster[Order Contribution Margin])

With this, I can slice this average by time dimension (from the order master), customerType, etc.