incorrect measure value for certain rows

[Rose111]

I have created a matrix table with rows gowdown(gowdown mapping),product group(product group mapping) and stockitem(allstock). The columns are closing quantity,sales amount,quantity sold and requirement.Here i am getting the correct value for gowdown and product group. but for some shop the requirement value is showing the same for all the stockitem. requirement isa measure created in re-order table. gowdown mapping and product group mapping have an active relationship between reorder table. the relationship with stockitem table is inactive.
How can I get the correct value for the requirement?
Below is the requirement measure:

Requirement = CALCULATE('Sales report'[Avg Sales Qty in reorder period])+SUM(ATReorderReportGrpCmpODBCMain[$MinimumDisplayQuantity])-SUM(AllStockSummaryBatchGrpODBC[$ClosingQty])-SUM(ATSCTotalTransitItemWiseCollODBC[TransitQty])-SUM(CMPSPurchaseOrderVchCollMain[Latest Open PO Qty])

[note:I have used the same requirement for another matrix table with rows gowodwn,product group and supplier(product group mapping table) it return the correct value for every rows]

stockitem value

gowdown and product group value

[Anonymous]

Hi @Rose111 ,

Please try:

Requirement = 
CALCULATE(
    'Sales report'[Avg Sales Qty in reorder period] +
    SUM(ATReorderReportGrpCmpODBCMain[$MinimumDisplayQuantity]) -
    SUM(AllStockSummaryBatchGrpODBC[$ClosingQty]) -
    SUM(ATSCTotalTransitItemWiseCollODBC[TransitQty]) -
    SUM(CMPSPurchaseOrderVchCollMain[Latest Open PO Qty]),
    USERELATIONSHIP('StockItemTableName'[StockItemColumnName], 'RelatedTableName'[RelatedColumnName])
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Rose111]

Hi ,
 Thankyou for your reply. I can't share the file because I am working on the remote desktop.I applied the above equation in the measure and it's not working. 
The logic is I want to get the count of requirement for the gowdown,product group and stockitem for each shop. which means that how much products are needed for each shop. here the gowdown and product group requirements are getting correctly for each shop. And stockitem list giving me the same value repeatedly as present in the product group. for eg .if product group value for product A IS 100, Then every stockitem under the product A is showing 100. 

[Anonymous]

Hi @Rose111 ,

Try it.

Requirement = 
CALCULATE(
    'Sales report'[Avg Sales Qty in reorder period] +
    SUM(ATReorderReportGrpCmpODBCMain[$MinimumDisplayQuantity]) -
    SUM(AllStockSummaryBatchGrpODBC[$ClosingQty]) -
    SUM(ATSCTotalTransitItemWiseCollODBC[TransitQty]) -
    SUM(CMPSPurchaseOrderVchCollMain[Latest Open PO Qty]),
    ALLEXCEPT('GowdownMappingTable', 'GowdownMappingTable'[GowdownColumn]),
    ALLEXCEPT('ProductGroupMappingTable', 'ProductGroupMappingTable'[ProductGroupColumn]),
    ALLEXCEPT('StockItemTable', 'StockItemTable'[StockItemColumn])
)

Best Regards,

Neeko Tang

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