Forum Discussion

apohl1's avatar
apohl1
Icon for Helper II rankHelper II
11 months 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 ...
  • v-saisrao-msft's avatar
    v-saisrao-msft
    11 months 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.

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

This worked! Thank you so much!!