Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Table Visual Showing Wrong Data

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 

Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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

      Thank you for your reply amitchandak .

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

      • v-xulin-mstf's avatar
        v-xulin-mstf
        Icon for Community Support rankCommunity Support
        5 years ago

        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.