Difference in Total Revenue amount

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.

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