Table Visual Showing Wrong Data

[Anonymous]

Following is my data model.

whereas FactSales connect to DimItem table via [ItemId] and DimItem table connects to CurrentHOSStock via [ItemID]. 

When I use DimItem[product code] and CurrentHOStock[BatchID] and TOTAL QTY = SUM(FactSales[UnitQty]) as measure in table I get following results.

For selected product code, 604903 giving almost all BatchIDs but actually there is only one BatchID for mentioned product code.

Sample pbix file is included.

sample.pbix 

[amitchandak]

@Anonymous , you can not take an ungrouped column CurrentHOStock[BatchID]  as this not joined to Factsales. You can take min or max of that

[Anonymous]

Thank you for your reply @amitchandak .

How can group columns and take desired results? Please help me.

[v-xulin-mstf · Accepted solution]

Hi @Anonymous,

You can merge the three tables by the common column ID and then group them to calculate sum.

After merging into new_table, you can try measure as:

TOTAL QTY = 
CALCULATE(
    SUM(FactSales[UnitQty]),
    FILTER(
        ALL(new_table),
        'new_table'[Product code]=MAX('new_table'[Product code]) && 'new_table'[BatchID]=MAX('new_table'[BatchID])
    )
)

Best Regards,
Link

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