Creating a DAX Measure to Combine Volume Data and ASP for Product Sales Report with Aggregation

[apohl1]

I need assistance in creating a DAX measure that combines volume data by product code (stored in Dataset 1) with the Average Selling Price (ASP) (stored in Dataset 2). The product codes in Dataset 2 are linked to Dataset 1 via a separate table (Dataset 3), which contains the equivalent product codes and has a direct relationship with Dataset 2.

The goal is to generate a report that displays sales based on volume from Dataset 1 and ASP from Dataset 2. The DAX measure should calculate sales per product code, and it should also be capable of aggregating these individual sales figures to provide a total sales amount across all product codes.

Thank you in advance for your help!

[v-saisrao-msft]

Hi @apohl1,

I’ve recreated the issue using sample data and configured the model with Volume, ASP, and a Mapping table. After setting up the relationships and creating the DAX measure, I obtained the expected output. I’ve attached the PBIX file for your reference.

Total Sales = 
SUMX (
    'Volume Data',
    'Volume Data'[Volume] *
        LOOKUPVALUE (
            'ASP Data'[ASP],
            'ASP Data'[ProductCode2],
            RELATED ( 'Mapping Table'[ProductCode2] )
        )
)

Thank you.

[apohl1]

Thank you! Unfortunately, I should have clarified that each product code has multiple rows, so the LOOKUPVALUE function doesn't work in this context. I tried the DAX below, but it's not summing the total correctly and it's not very efficient in terms of performance.

Total Sales =

SUMX(

    SUMMARIZE(

        'Dataset1',

        'Dataset1'[Product code],

        "Volume", SUM('Dataset1'[Volumes])

    ),

    VAR ProductCode = 'Dataset1'[Product code]

    VAR Volume = SUM('Dataset1'[Volumes])

    VAR ASP =

        CALCULATE(

            Dataset2[ASP],

            RELATEDTABLE('Dataset3'),

            'Dataset3'[Product code] = ProductCode

        )

    RETURN

        Volume * ASP)

[v-saisrao-msft · Accepted solution]

Hi @apohl1,

Please try the DAX measure below. I received the following output.

Total Sales =
SUMX (
    VALUES ( 'Volume Data'[ProductCode] ),
    VAR Volume =
        CALCULATE ( SUM ( 'Volume Data'[Volume] ) )
    VAR ASP =
        CALCULATE (
            AVERAGE ( 'ASP Data'[ASP] ),
            TREATAS ( VALUES ( 'Volume Data'[ProductCode] ), 'Mapping Table'[ProductCode] )
        )
    RETURN
        Volume * ASP
)

Thank you.

[apohl1]

This worked! Thank you so much!! 

[v-saisrao-msft]

Hi @apohl1,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further

Thank you.

[v-saisrao-msft]

Hi @apohl1,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.