Forum Discussion

apohl1's avatar
apohl1
Icon for Helper II rankHelper II
1 year ago
Solved

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

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's avatar
    v-saisrao-msft
    1 year ago

    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.

6 Replies

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
    apohl1
    Icon for Helper II rankHelper II
    1 year ago

    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's avatar
      v-saisrao-msft
      Icon for Community Support rankCommunity Support
      1 year ago

      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's avatar
        apohl1
        Icon for Helper II rankHelper II
        1 year ago

        This worked! Thank you so much!! 

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi apohl1,

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

     

    Thank you.