I have the following tables in Power BI :

Dim Client
Dim Account
Dim Contract
Fact Ledger

The relationships and the Cross-filter direction :

Now I am using a simple measure from Fact Ledger :

MyMeasure = SUM( 'Fact Ledger' [Ledger Amount])

Everything works good unless for one record where the measures gives the double of the result :

This is when I use the Ledger Amount column :

Account Id      Client Id   Ledger Date     Ledger Amount
123             ABC         01/01/2024      50
 

This is when I use the measure MyMeasure :

Account Id      Client Id   Ledger Date     MyMeasure
123             ABC         01/01/2024      100
 

I even verified in the SQL database and I don’t understand why I am getting this behavior.

This is the query from the performance analyzer when I add the measure :

// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('Dim Account'[Client Id])),
SEARCH("vfinc_00702703", 'Dim Account'[Client Id], 1, 0) >= 1
)

VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('Fact Client Ledger'[Ledger Client Date])),
AND(
'Fact Client Ledger'[Ledger Client Date] >= DATE(2024, 6, 6),
'Fact Client Ledger'[Ledger Client Date] < (DATE(2024, 6, 6) + TIME(0, 0, 1))
)
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP(
'Dim Account'[Account Id],
'Dim Account'[Client Id],
'Fact Client Ledger'[Ledger Client Date]
), "IsGrandTotalRowTotal"
),
__DS0FilterTable,
__DS0FilterTable2,
"Ledger_Client_Total_Amount", '_Measures'[Ledger Client Total Amount]
)

VAR __DS0PrimaryWindowed =
TOPN(
502,
__DS0Core,
[IsGrandTotalRowTotal],
0,
'Dim Account'[Account Id],
1,
'Dim Account'[Client Id],
1,
'Fact Client Ledger'[Ledger Client Date],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
[IsGrandTotalRowTotal] DESC,
'Dim Account'[Account Id],
'Dim Account'[Client Id],
'Fact Client Ledger'[Ledger Client Date]