Forum Discussion

Rose111's avatar
Rose111
Frequent Visitor
3 years ago

incorrect measure value for certain rows

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 requirem...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Rose111
Frequent Visitor
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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.