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 ...
  • 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.

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
    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!!