Count frequency of column values by another column then create custom column based on the totals

[hfree]

As the title confusingly suggests, I am having a little trouble figuring out what I need to do. I have researched and explored the 'Group By' features amongst some other custom column DAX soltions but no slice. 

I essentially have table like so: 

Table 1

Location	Product	Names
London	iPhone	Jerry
Manchester	Andriod	Mark
London	Android	Jerry
Berlin	iPhone	Mark
London	iPhone	Jerry

I would like to count up my product and names by location and have that count for each row, like so:

Location	Product	Names	Total Product	Total Names
London	iPhone	Jerry	2	3
Manchester	Andriod	Mark	1	1
London	Android	Jerry	1	3
Berlin	iPhone	Mark	1	1
London	iPhone	Jerry	2	3

I am at a loss on a solution, I'm sure it must be simple but when I attempt to group by and then merge my query back to my main Table, I end up with a whole bunch of duplicates. I hope this makes sense! Thanks

[AilleryO · Accepted solution]

Hi,

One easy and quick solution, would be to Duplicate your table, do he group by in one of the copy, then merge back with your original table.

You'll find with this link a more clean solution in code M :

https://community.powerbi.com/t5/Desktop/How-to-count-duplicate-values-in-M/m-p/312604

Hope this helps

[hfree]

Thank you for the link! When duplicating my table, grouping by and merging back with my original table, my columns are jumping from 300 to 999+. The values appear to be duplicating and I'm sure it's something to do how I am merging it? 

[AilleryO]

Does it have something to do with the column or column(s) you're using to merge ?

You should be using Left Outer with on top your original table with the detailed lines, 

and at the bottom your table that you just grouped.

Let us know

[hfree]

I was halfway through typing a long response with screenshots as to what process I was taking when I clocked your columns(s)! I wasn't merging by multiple columns, only by my location, which is probably why they were duplicating! So you so much for your help today Aillery and for such fast replies!

[AilleryO]

Do not forget to Accept as Solution 🙂

[AilleryO]

Hi,

I'm not sure about the outcome you expect, but let's try some things :

Create a new measure to count and use ALL function to remove unwanted filters (those coming from your visual).

Like :

Count by Product = CALCULATE(
    COUNT( TableTest[Product]) , ALL(TableTest[Location]))

 In the formula above, I just remove the filters from the location, to count the product without "looking at" the different location values.

Or could be :

Count by Names = CALCULATE(
    COUNTA( TableTest[Names]) , ALL(TableTest[Location], TableTest[Product]))

In this case I remove filters from the columns Location and Product.

Sorry not to be more specific but when I'm looking at your result table I'm wondering what it really counts ?

Hope those formula will help ?

Let us know...

[hfree]

Thank you for such a quick response! It's much appreciated as I have been racking my brains. I am not sure I have explained the problem well enough. 

I am looking to do this inside Power Query itself. So taking the following table below:

I would like to count the frequency a buyer_name purchases a product per location and then add a column onto the table with the total number of times somebody has purchased something but have that repeat row by row. 

So for the above example in table_1, I would then like to add two columns to the right which count the number of times my buyer has purchased at the location and the products sold at my location. With the desired output being so: 

So for 'Count_Buyer_for_location', jerry has purchased from London 4 times, so everytime 'London' and 'Jerry' are in the same row, I would like it to be the total. Does that make sense? 

I am looking to do this in Power Query as part of a wider plan to use these totals. Thank you