topic Re: Creating a DAX Measure to Combine Volume Data and ASP for Product Sales Report with Aggregation in DAX Commands and Tips

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

apohl1 — Fri, 19 Sep 2025 10:16:56 GMT

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!

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

v-saisrao-msft — Fri, 19 Sep 2025 11:59:43 GMT

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.

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

apohl1 — Fri, 19 Sep 2025 13:08:15 GMT

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)

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

v-saisrao-msft — Mon, 22 Sep 2025 05:17:46 GMT

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.

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

v-saisrao-msft — Thu, 25 Sep 2025 07:00:25 GMT

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.

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

v-saisrao-msft — Mon, 29 Sep 2025 04:40:52 GMT

Hi apohl1,

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

Thank you.

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

apohl1 — Tue, 30 Sep 2025 07:58:53 GMT

This worked! Thank you so much!!