Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Columns from multiple tables

 

Just started with Power BI.

 

I have an item table: item id, product code, category, defect type 1, defect type 2, defect type 3, total defect count

Item id is unique. Multiple item ids can belong to a product code or category.

Most of my reporting happens at a product level.

 

I also have another table from another source: product code, sales

 

How can I create product level graphs with defect count, sales together. I am having trouble bringing values from two table together.

 

Please help.

Need Help

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    hi Anonymous 

    you can add a measure into your Item table like

    Measure = calculate(sum('Product table'[Sales]),FILTER(ALL('Product table'),'Product table'[product code]=SELECTEDVALUE('Item table'[product code]))

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      Anonymous I would recommend to focus on data model following best practice and good model can take you long way.

       

      So add another table in your model, which is list of unique product code and set relationship between product code with other two tables.

       

      In nutshell, you will have 3 tables:

       

      Product Code table (unique products)

      Defect table

      Sales table

       

      and Relationship between these tables will be

       

      Product Code table connected to Defect Table -> 1 to Many relationship (1 on product side, and many on Defect table side)

      Product Code table connected to Sales Table -> 1 to Many relationship (1 on product side, and many on Sales table side)

       

      Once above is all set, just simple take any visual, say  Table visual,

       

      On Values section, drop product code from product table and defct and sales values from respective table and you will see the result you want. From here onwards, it willl be easy to build any analysis dependent on product code.

       

      Hope it helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    have you joined tables in modelling tab (one to many or many to one)

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi mentos, 

    Yes, you could refer to above suggestions to create relationship or join two tables. If possibel, could you please inform me more detailed information (such as your sample data, you could use simple data instead of real data to explain this and your expected output), then I will help you more correctly.

    Best Regards,
    Zoe Zhi

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