Wrong results on subtotals in Analyze in Excel

[GriRim]

Hi all,

There is simple sales report. Semantic model - star schema. Fact table in the middle and dimensions around. Relationships one to many, exept Customer table  - both (I needed to changed it, since Excel couldn't filter out data properly).

We connected this model to Excel and weird things start to happen.

Somehow Customer hierarchy II (customer dimension) is not working properly.

If I list all Customer Hierarchy II and sum just Revenue (revenue column and select sum function), I'm getting amount for Allocate me and Not assigned:

If I just filter out those two values, I have different result (the right ones, that I see in Power BI report):

Does someone else encountered similar issues? Have you found the work around?

[v-csrikanth]

Hi @GriRim 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @GriRim 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth · Accepted solution]

Hi @GriRim 
I apologize that the issue persists even after using a single-direction relationship and trying both Excel’s built-in Sum and a DAX measure. 

Please do check the below poinnts the might resolve your issue:

• Replace the bidirectional relationship on the Customer table with a single-direction one (from Customer to Fact).
• Create a proper DAX measure in the model for revenue:
  Total Revenue = SUM('FactTable'[Revenue])
• Use this Total Revenue measure in Excel PivotTables instead of dragging the raw column and applying Excel’s SUM.
• Add a calculated column in the Customer table to flag missing or unlinked records:
  Customer Status = IF(ISBLANK(Customer[CustomerID]), "Not assigned", "Valid")
• Use this “Customer Status” in your Excel rows/filters instead of relying on hierarchy levels.
• Avoid dragging hierarchies with missing levels directly into PivotTables, as they can misaggregate data.
• Recheck the Excel PivotTable totals — they should now match what Power BI shows.

This should resolve the mismatch between what you see in Excel vs. Power BI.

If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @GriRim 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[GriRim]

Hi @v-csrikanth   the isue sill exists with singe - direction relationship and using excel sum or Dax sum - also doesn't bring any changes. Since we have correct values in Power BI, I have recomendet business not to use Excel with this connection.

[v-csrikanth]

Hi @GriRim 
Thanks for reaching out to Fabric Community.
Please check the below table that might helps to clear out your doubts.

Recommendation	Why it helps
Use single-direction relationships	Removes ambiguity in filtering, especially in Excel
Create explicit DAX measures e.g. Total Revenue = SUM(Fact[Revenue]))	Ensures filter context is applied properly
Avoid using Excel's implicit aggregation	Implicit SUM/COUNT in Excel doesn't always honor model filters correctly
Use Power BI for complex filtering/reporting	Power BI visuals interpret the data model better
Test your Excel report thoroughly with different filters	Catch inconsistencies early

You must keep the bidirectional relationship due to business logic, consider writing your measure like:

Total Revenue (Filtered) =CALCULATE(SUM(Fact[Revenue]), REMOVEFILTERS(Customer[Customer Name])  // Or restrict to valid customers)

Or even

Total Revenue (Clean) =CALCULATE(SUM(Fact[Revenue]),NOT(ISBLANK(Customer[Customer ID]))  // Exclude 'Not assigned' scenarios)

If the above information helps you, please give us a Kudos and marked the Accept as a solution.

Best Regards,
Community Support Team _ C Srikanth.

[nathancwatkins]

That being so, you can add the filters to your CALCULATE expression, like

       CALCULATE (

            [Sales Amount],

            FILTER ( ALL ( 'Product'[Color] ), 'Product'[Color] IN { "Red", "Blue" }

--Nate 

[GriRim]

Thanks @nathancwatkins  and @Omid_Motamedise for you replay and just for the record I do have respect for Italians 🙂

I've changed to relationship to single, and issue still here.

What I managed to found is that this table is filtered by Customer Group (from stame Customer Dim table). And somehow amount of Not assigned - 223,065 is not responding to filtered value and 56,97 is amount wothout filterd Customer Group value. During the screenshots I haven't done anything, just expanded and collapsed the row and amount differs.

[Omid_Motamedise]

The issue you're seeing with Customer Hierarchy II is almost certainly due to the bidirectional relationship you created between the Customer dimension and the Fact table. While this change may have fixed one issue in Excel filtering, it's introducing ambiguity and potential filter propagation problems.

In Excel, especially when using PivotTables connected to a semantic model, bidirectional relationships can lead to unexpected totals — like those you’re seeing under "Allocate me" and "Not assigned". This happens because Excel handles filter context differently from Power BI visuals.

[nathancwatkins]

Sounds like you've fallen victim by not heeding the Italian's advice: "Do not use bidirectional relationships unless you really understand the impact that they will have on your calculations." Best option is to fix your semantic model. Otherwise, try using  CALCULATE(SUM('Sales'[Revenue]), USERELATIONSHIP('CustomerTable'[Customer], 'Sales'[Customer]))

Also, do you really need to have an hierarchy? If your semantic model is tight, you can drag the right fields onto the page just like pivot tables. 

Or something like that.

--Nate