Difference in Total Revenue amount

[Anonymous00729]

Hi all, I'm new to Power BI. I did a project in Excel and want to replicate this in Power BI. The problem that I'm faced with, is with the Total Revenue amount. In the screenshot below, the correct Total revenue amount should be $698812.33

The source file was an Excel workbook with Transactions, Product and Store Location data all combined into a single sheet. So I split these up into separate tables to create a data model which looks like this...

To calculate Total Revenue, I used a DAX measure:

Total Revenue = SUMX('Transactions','Transactions'[transaction_qty]*RELATED('Product'[unit_price]))

But, when I used this formula, it gives me answer below.

Please can someone assist me to figure out where I'm going wrong, thank you.

[johnt75 · Accepted solution]

There's nothing wrong with the measure you wrote, and there doesn't appear to be anything wrong with the model you've created, so I think the problem is likely in the underlying data.

I would start by creating a table visual with month name and [Total Revenue] and compare that with the results from Excel. If there is a blank row in Power BI then that would indicate that either there are rows missing a date or that there are rows with dates outside the date range covered by your date table.

You might also want to import the Excel data directly into Power BI without any transformations. That way you could write measures against both versions of the data and compare the results.

[Nasif_Azam · Accepted solution]

Hey @Anonymous00729 ,

You're on the right track by building a star schema and using DAX. The issue you're facing a mismatch in total revenue between Excel ($698,812.33) and Power BI ($700,779.74) likely stems from data model or calculation inconsistencies introduced during normalization. Here's a detailed breakdown of what might be going wrong and how to troubleshoot it.

Quick Summary of Your Current Setup

• Excel Calculation: Flat file with correctly aggregated revenue = $698,812.33

• Power BI Model: Star schema with:

  • Transactions fact table

  • Product, Store Location, Date dimension tables

• DAX Measure:

  Total Revenue = SUMX('Transactions', 'Transactions'[transaction_qty] * RELATED('Product'[unit_price]))

 

1. Join Multiplication (Many-to-One Mismatch or Duplicates in Product)

If Product[product_id] is not unique, or there are hidden duplicates in Product, the RELATED() function will return multiple matches, causing the measure to overcalculate.

Action:

• Go to Product table → Check for duplicate product_ids.

  Duplicates = COUNTROWS(FILTER(Product, CALCULATE(COUNTROWS(Product)) > 1))

• Ensure that Product[product_id] is set as a primary key and is unique.

2. Rounding Issues

Excel sometimes rounds differently than Power BI. But your difference (~$2,000) is too large for rounding errors so this is unlikely the core issue.

3. Hidden or Extra Rows in Power BI Transactions

Power BI may be reading extra rows due to:

• Empty or malformed rows

• Rows filtered out in Excel that aren't in Power BI

Action:

• Compare row count in Excel vs Power BI for the Transactions table.

  TotalRows = COUNTROWS('Transactions')

4. Currency Format or Separator Interpretation

In your Power BI card visual, 700,78K is shown this is a European decimal separator (, for decimals, . for thousands). It might just be a visual formatting issue, not a logic issue.

Action:

• Check locale and format settings under Model → Format → Currency

5. Extra Products or Incorrect Prices

Since you're multiplying by RELATED('Product'[unit_price]), ensure:

• There are no extra rows with higher unit prices

• Products align correctly with transactions

Action:
Try validating with a basic table:

• product_id, transaction_qty, unit_price, transaction_qty * unit_price

Alternate Approach: Use MERGE in Power Query

To ensure that unit_price is correctly assigned per transaction row:

1. In Power Query:

   • Merge Transactions with Product using product_id

   • Create a new column transaction_amount = transaction_qty * unit_price

2. Then SUM that column in Power BI

Recommended Fix

If you're sticking to DAX:

Total Revenue = 
SUMX(
    ADDCOLUMNS(
        'Transactions',
        "Price", RELATED('Product'[unit_price])
    ),
    'Transactions'[transaction_qty] * [Price]
)

Then verify by comparing this measure against individual rows in a table visual.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Anonymous00729]

Hi @Nasif_Azam , that's exactly what happened. The unit_price column misled me to believing that only one unit_price existed for product_id 9 so when I removed duplicates, I thought I'd be removing the duplicates of just one unique value only to realise that values others existed to but, didn't show up in the column statistics.

Thank you so much for pointing me in the right direction. I greatly appreciate it.

[johnt75 · Accepted solution]

There's nothing wrong with the measure you wrote, and there doesn't appear to be anything wrong with the model you've created, so I think the problem is likely in the underlying data.

I would start by creating a table visual with month name and [Total Revenue] and compare that with the results from Excel. If there is a blank row in Power BI then that would indicate that either there are rows missing a date or that there are rows with dates outside the date range covered by your date table.

You might also want to import the Excel data directly into Power BI without any transformations. That way you could write measures against both versions of the data and compare the results.

[Anonymous00729]

Hi @johnt75 thank you so much for your help. This was actually a guided project done in Excel. Whilst I followed the instructor's exact steps in Excel, he didn't split the data up into different tables and add to the data model. He was trying to demonstrate the use of PivotTables. So, I wanted to take a different approach and try this in Power BI, when I ran into this issue. Upon closer inspection of my data, there was product_id 9 which was the problem. It shows 1 distinct and 0 unique values for the unit_price column which isn't true because in the screenshot below, there were other values present that I wasn't aware of.

I initially removed any duplicates and this is what caused me to have a slightly inflated figure for Total Revenue. When I removed the 'removed duplicate' step, I got the same value as I did in Excel.

Thank you for pointing me back to my data, I greatly appreciate it.