cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jkevins
Frequent Visitor

Many to one filtering while aggregating at the same time

Hi all,

 

I have 2 tables, A and B. A looks as follow:

id_uniquevar 1 var 2
110
210
301
............

 

And B looks as follow (not all "id_unique" of table A is in "id" and "id" is also not unique):

idproducthave bought?
1A1
3B0
3C1
...........

 

I enabled the many-to-one relationship between the two tables using the column id_unique and id respectively.

 

I'd like to get the % of products purchased per product. One way that I've tried is to use the column "product" in table B as axis and average of "have bought" as values. However, this won't enable me to use table B to filter A. Another method I tried was to remove all the rows with 0s in "have bought?" and it works to filter, but the problem is that now I can get the count but not the %. I tried to use a measure from Table A to count number of rows in A and then divide count in b with that measure, but it would only take into account all the rows that has common id between the two tables. Would love to get your feedback on this. Thank you so much!

1 REPLY 1
Ashish_Mathur
Super User
Super User

Hi,

Your question is not clear.  What are the Var 1 and Var 2 columns in Table 1?  What end result are you expecting?  Please show that clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!