Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiply Quantity in one table by Price in another table

What's the best way to structure my model in this scenario:   I have a Quantity table that has quantites by date that is not continuous:   The date column in connected to my calendar table....
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi Anonymous 

     

    Download sample PBIX file with the following data/tables

     

    Can you please show what you mean by the amounts don't aggregate correctly?

    I've set up a DateTabe and 2 tables as you described above: Quantity and Price.

    With a relationship from the DateTabe to the others, everything works as it should in my model.

     

     

    I'm using this measure to calculate Qty * Price.

    Tot Price := SELECTEDVALUE('Quantity'[Qty]) * SELECTEDVALUE('Price'[Price])

     

    BTW - you don't need to show the date hierarchy unless you need to.  Change it to just show the date 

     

    Regards

    Phil

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

Hi,

I would have implemented your own suggestion of bringing in the price to the quantity table via the LOOKUPVALUE() function.  Then write a SUMX() measure to get the result in your visual.  Be careful to drag the Date/Year/Month from the Calendar Table.

Hope this helps.