Sum of products in one-to-many relation

[oester]

Hi folks,

in a marketing scenario I have several marketing touchpoints, that can be assigned to one order.

I want a measure that determines the financial impact of a touchpoint to the order value (attribution):

"SUM(1/#touchpoints per order * order value)"

I tried a measure like this

Attribution :=CALCULATE(SUMX('Touchpoint';DIVIDE(1;COUNTROWS('Touchpoint'))*'Order'[Value]))

but the DIVIDE(1;COUNTROWS('Touchpoint'))-part must be evaluated in a row-context of a customer journey or order instead of the the whole table.

I can manage it by creating a calculated column in customer journey with  DIVIDE(1;COUNTROWS('Touchpoint')) but I cannot use calculated columns since then filters can't be used later.

Is there any other way to solve this?

Thank you

Oliver 

[v-jiascu-msft]

Hi @oester,

Since the 'order'[Value] is in the 1-side, you can add a MIN to it. Try this please.

Attribution =
SUMX (
    'Touchpoint',
    DIVIDE ( 1, COUNTROWS ( 'Touchpoint' ) ) * MIN ( 'Order'[Value] )
)

You also can try iterate the other tables.

Attribution 2 =
SUMX (
    'Order',
    DIVIDE ( 1, COUNTROWS ( 'Touchpoint' ) ) * MIN ( 'Order'[Value] )
)

Please check the pbix file here: https://1drv.ms/u/s!ArTqPk2pu-BkgRUujD6zCX2WTzeS

I guess the measure is enough.

Measure =
DIVIDE ( 1, COUNTROWS ( 'Touchpoint' ) ) * MIN ( 'Order'[Value] )

Best Regards!

Dale

[oester]

Hi Dale

thanks for your reply!

However, that's not exactly what I'm looking for. 

You can see my scenario in the screenshot (or pbi), Each touchpoint is assigned the attributed order value ratio by calculating the product of the param and the order value. The param value is nothig but 1/#touchpoints per order. 

However, if I filter the touchpoints the param values must adjust to the new #touchpoints per order to still attribute the full order value.

So, since the param is a calculated column I know this cannot work. How can I include this parameter directly in a measure?

Thanks for your help

Oliver

Feel free to check this pbi file:

https://1drv.ms/u/s!AhfGW8TQ_Gs4as9Tjije9dXlc18

[v-jiascu-msft]

@oester,

Hi Oliver,

1. Create a new table TouchPointIDs.

TouchPointIDs =
ALL ( Touchpoint[TouchPointID] )

2. Establish relationships.

3. Create a measure.

Attribution =
VAR touchpointsPerOrder =
    CALCULATE (
        COUNT ( Touchpoint[TouchPointID] ),
        ALL ( 'Touchpoint'[TouchPointID] ),
        ALL ( Touchpoint[Attribute] ),
        ALL ( Touchpoint[Year] )
    )
RETURN
    DIVIDE ( 1, touchpointsPerOrder, 0 ) * MIN ( 'Order'[Value] )

Please check this file for details: https://1drv.ms/u/s!ArTqPk2pu-BkgSN7zRilxJy2zP2P

Best Regards!

Dale