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 CurrentHO...
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.

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.