(Direct query) measures total not equal to sum of details

[AdeleSS]

Hi Everyone,

I've searched for similar issues posted by others and tried some of the solutions in those answers but failed. And I guess it might because my data is "Direct Query" not "import"?  I would appreciate any help regarding this issue.

Issue description: 

PR LocalCurrency Row is calculated by item level and it can calculate correctly but the total is not what I want.

I've tried several function including following but all shows blank

PR LocalCurrency = SUMX(VALUES('Fact Shipment Detail'[Item Number]),[PR LocalCurrency ROW])

PR LocalCurrency = if(HASONEVALUE('Fact Shipment Detail'[Item Number]),[PR LocalCurrency ROW], BLANK())

The data comes from company data source and I can't change any of it. Complicated relationship as below.

Many Thanks!

[Anonymous]

Hi @AdeleSS ,

Based on your description, you seem to be experiencing common issues related to aggregation and context in DAX calculations, especially in a DirectQuery environment. You can follow the steps below to troubleshoot the problem:

1. The problem of mismatched totals may be due to the context in which the calculation is performed. Consider using CALCULATE to explicitly define the filtering context of the measure.

PR LocalCurrency =
CALCULATE (
    [PR LocalCurrency ROW],
    ALLEXCEPT ( 'Fact Shipment Detail', 'Fact Shipment Detail'[Item Number] )
)

2. Recheck your data model to ensure that the relationships are defined correctly and that no unexpected filters are being applied through those relationships. The behaviour of Power BI in handling many-to-many relationships or inactive relationships can affect the totals. Managing Relationships in Power BI Desktop:

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sayaliredij]

HI @AdeleSS ,

It would help if you can share sample data and if possible PBIX which you build with it.

You can remove sensitive information and replace it with sample size data

Thanks and Regards,

Sayali

Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

[AdeleSS]

I think the type of data ( import vs direct query) matters to this issue, so I'm afraid I can't share you the data because its our company dataset using 'power bi semantic models'.

Do you have any possible methods to share that I can try by myself?

Thank you for your reply.