How to filter measures by using 2 slicers to compare customers?

[t_guet01]

Hi guys,

I am currently trying to figure out the following: how would my revenue change if customer A got the prices customer B has?

Please find the file here: https://www.dropbox.com/sh/2vxnjdgt8vk0pxs/AABo2Mv3XMw9Mz-ujIUYrqSFa?dl=0

I want this comparison to be dynamic, i. e. I set up two slicers to select the two customers I want to compare. My input table is a transaction table.

In the example of the screenshot, I compare Customer "123" with customer "456". They have the products "ABC", "DEF", "GHI", and "JKL" in common. How would the revenue of customer "123" change if they get the (average) prices customer "456" gets? Since customer "456" does not purchase product "MNO", I would like to assume the price stays stable for customer "123" for product "MNO", i.e. 45,30 €. Since product "DEF" is purchased twice by customer "456", I assume the weighted average to be customer "123"'s price, i.e 11,70 €. 

Revenue customer A with own prices: 3929 €

Revenue customer A with "456" prices: 3890 €

Target measure: -0,99 % = (3890 - 3929) / 3929

Any ideas?

Thanks very much!

Best

Tom

[AlexisOlson · Accepted solution]

Here's a way to calculate the alternative revenue:

Customer 1 Alt Revenue = 
VAR Cust1 = SELECTEDVALUE ( CustomerSelection1[CustomerID] )
VAR Cust2 = SELECTEDVALUE ( CustomerSelection2[CustomerID] )
VAR Products1 = CALCULATETABLE ( VALUES ( SalesData[ProductID] ), SalesData[CustomerID] = Cust1 )
VAR Products2 = CALCULATETABLE ( VALUES ( SalesData[ProductID] ), SalesData[CustomerID] = Cust2 )
VAR SharedProductsRev =
    ADDCOLUMNS (
        INTERSECT ( Products1, Products2 ),
        "@Qty",   CALCULATE ( SUM ( SalesData[Quantity] ), SalesData[CustomerID] = Cust1 ),
        "@Price", CALCULATE ( [AveragePrice], SalesData[CustomerID] = Cust2 )
    )
VAR Cust1OnlyProductsRev =
    ADDCOLUMNS (
        EXCEPT ( Products1, Products2 ),
        "@Qty",   CALCULATE ( SUM ( SalesData[Quantity] ), SalesData[CustomerID] = Cust1 ),
        "@Price", CALCULATE ( [AveragePrice], SalesData[CustomerID] = Cust1 )
    )
RETURN
    SUMX ( SharedProductsRev, [@Qty] * [@Price] )
        + SUMX ( Cust1OnlyProductsRev, [@Qty] * [@Price] )

From here, defining the Target measure should be straightforward.

[CNENFRNL · Accepted solution]

Tricky game of playing with filters,

[CNENFRNL · Accepted solution]

Tricky game of playing with filters,

[CNENFRNL · Accepted solution]

Tricky game of playing with filters,

[AlexisOlson · Accepted solution]

Here's a way to calculate the alternative revenue:

Customer 1 Alt Revenue = 
VAR Cust1 = SELECTEDVALUE ( CustomerSelection1[CustomerID] )
VAR Cust2 = SELECTEDVALUE ( CustomerSelection2[CustomerID] )
VAR Products1 = CALCULATETABLE ( VALUES ( SalesData[ProductID] ), SalesData[CustomerID] = Cust1 )
VAR Products2 = CALCULATETABLE ( VALUES ( SalesData[ProductID] ), SalesData[CustomerID] = Cust2 )
VAR SharedProductsRev =
    ADDCOLUMNS (
        INTERSECT ( Products1, Products2 ),
        "@Qty",   CALCULATE ( SUM ( SalesData[Quantity] ), SalesData[CustomerID] = Cust1 ),
        "@Price", CALCULATE ( [AveragePrice], SalesData[CustomerID] = Cust2 )
    )
VAR Cust1OnlyProductsRev =
    ADDCOLUMNS (
        EXCEPT ( Products1, Products2 ),
        "@Qty",   CALCULATE ( SUM ( SalesData[Quantity] ), SalesData[CustomerID] = Cust1 ),
        "@Price", CALCULATE ( [AveragePrice], SalesData[CustomerID] = Cust1 )
    )
RETURN
    SUMX ( SharedProductsRev, [@Qty] * [@Price] )
        + SUMX ( Cust1OnlyProductsRev, [@Qty] * [@Price] )

From here, defining the Target measure should be straightforward.