Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Adding a column based on the value count in a different table - New User!

Hello,

 

   I am new to PowerBI. I have a Vendor table. I want to add a column to this table that will list the total count of transaction for each vendor based on the count of transactions in our ShoppingCart table. Here is what I want to do:

 

TotalTransactions =if(ShoppingCart[manufID] = [ManufID], count(ShoppingCart[ManufID]))

 

The error I get leads me to believe that I need to do this count for each vendor in the vendor table, but I do not know how to do that. Can you help?

 

Cheers!

 

Peter

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 


    Try related the two tables with ManufId columns and create a column or measure something like this:

     

    Total Transactions in Shopping Cart =
Calculate(COUNTROWS('VendorManufacturer'[ManufID]),filter(ShoppingCart,ShoppingCart[ManufID]=Related(VendorManufacturer[ManufID])))

     

    However, if you need a definite solution, you could provide a sample pbix.

     

    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
      

8 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hi Anonymous ,

     

    Can you please add more details to your query like screenshot of data model you have, their relationships and what you are trying to achieve with some sample data?

     

    Thanks,

    Pragati

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for the quick reply. Here is a screen image:

      There is a 1:M relationship between the Vendor table and the Shopping Cart table.

       

      Peter

      • Pragati11's avatar
        Pragati11
        Super User
        6 years ago

        Hi Anonymous ,

         

        I don't think you need a calculation for this as you have a relationship between these 2 tables.

         

        1. Create a table visual
        2. Move your ManufID, VendorName column from Vendor table to this visual
        3. Move COUNT(ManufID) from ShoppingCart table to this visual.

        You should get the desired result.

         

        Thanks,

        Pragati