Calculating related product sales

[anandav]

Hi,

I have a data model as shown in t he below diagram.

I want to calculate the Main Product Sales by Customer.

I can enable bi-direction filtering between Product_Package_Sales and Main_Products and use the Main Product Price to get the right results.

I wanted to use a measure to achieve this.

When I use the below measure it is working. In SUMMARIZE funtion the Customer ID and Main Product ID are from respective dimension tables.

_M Main Product Sales by Customer =
SUMX (
    SUMMARIZE (
        Product_Package_Sales,
        Customers[Customer ID],
        Main_Product[Main Product ID]
    ),
    CALCULATE ( SUM ( Main_Product[Main Product Price] ) )
)

But when I use the Customer ID and Main Product ID from the Product_Package_Sales fact table in the SUMMARIZE funtion, the results are wrong. (Result shown in the green highlighted box)

_M Product Test2 =
SUMX (
    SUMMARIZE (
        Product_Package_Sales,
        Product_Package_Sales[Customer ID],
        Product_Package_Sales[Main Product ID]
    ),
    CALCULATE ( SUM ( Main_Product[Main Product Price] ) )
)

I would appreciate if anyone could explain why the M Product Test2 is not giving the right results.

[Phil_Seamark]

So just an aside, what happens if you add the following measure to your model and add it to the same table visual?

_M Main Product Sales by Customer (phil) = SUM ( Main_Product[Main Product Price] ) 
 

[Phil_Seamark]

HI @anandav

Is this a calculated measure being put in a Power BI visual (table?)

If so, then your working version is probably using the following two fields in the visual

 Customers[Customer ID],
 Main_Product[Main Product ID]

If you removed these fields from your table visual and replaced with these two fields 

      Product_Package_Sales[Customer ID],
      Product_Package_Sales[Main Product ID]

and used the [_M Product Test2] measure as well, it might start to work.

As an aside, I don't think you need to use SUMMARIZE in your measure, but let's see if this works first.

[anandav]

Hi @Phil_Seamark,

1] Is this a calculated measure being put in a Power BI visual (table?)

AV - Yes, these are cluclated measures in a Table visual.

2] If so, then your working version is probably using the following two fields in the visual

 Customers[Customer ID],
 Main_Product[Main Product ID]

AV- No. I am using the Product_Package_Sales[Customer ID] in the table visual.

3] If you removed these fields from your table visual and replaced with these two fields 

       Product_Package_Sales[Customer ID],
      Product_Package_Sales[Main Product ID]

AV- It is still not working. The  [_M Product Test2] is still giving the wrong result as shown in the original image.

4] So just an aside, what happens if you add the following measure to your model and add it to the same table visual?

_M Main Product Sales by Customer (phil) = SUM ( Main_Product[Main Product Price] )

AV- It is giving the same results as the [_M Product Test2] with both cases - a) Customer ID from Customers b) CUstomer ID from Product_Package_Sales