How to count values of one column by another column

[Anonymous]

Hello,

So if I have a table like this that has a list of ZIPs delivered to and the week they're delivered, except for the bold column. That's the column I need to build.

tRaw

ZIP		Week		Weekly Total
12345		1		3
12345		1		3
12345		1		3
67890		1		2
67890		1		2
12345		2		2
12345		2		2
67890		2		1

I've tried 

Weekly Total= 
CALCULATE(
   DISTINCTCOUNT(tRaw[ZIP]),
   ALLEXCEPT(tRaw,tRaw[Week])
   )

but what ends up coming out is

ZIP		Week		Weekly Total
12345		1		5
12345		1		5
12345		1		5
67890		1		5
67890		1		5
12345		2		3
12345		2		3
67890		2		3

Any help would be appreciated!

[AilleryO]

Hi,

You should do a COUNT not a DISTINCTCOUNT, which prevents you from havnig the expected result.

Both solutions hereunder, would do the trick :

As @parry2k proposed :

Weekly Total v2 = 
CALCULATE(
   COUNT(tRaw[ZIP]),
   ALLEXCEPT(tRaw,tRaw[Week],tRaw[ZIP])
   )

Or if you like to have another version of the solution, you could do :

Weekly Total v1 = 
CALCULATE(
   COUNT(tRaw[ZIP]),
   KEEPFILTERS(tRaw[Week]), KEEPFILTERS(tRaw[ZIP])
   )

Both soltions provides expected results. Let us know...

[Anonymous]

Hi @Anonymous 

I am so glad that you have solved your problem , please consider Accept it as the solution to help the other members find it more quickly.

Best Regard

Community Support Team _ Ailsa Tao

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

[parry2k]

@Anonymous you should add Zip column in ALLEXCEPT too:

✨ Follow us on LinkedIn and  to our YouTube channel

Check my latest video on Filters and Sparklines https://youtu.be/wmwcX8HvNxc

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous · Accepted solution]

Figured it out

Don't need DISTINCTCOUNT, COUNT worked perfectly

Column 2 = calculate(

count(tRaw[ZipCode]),

ALLEXCEPT(tRaw, traw[week], tRaw[ZipCode])

)

[Anonymous]

Like so?

Column =

CALCULATE(

DISTINCTCOUNT(tRaw[ZipCode]),

ALLEXCEPT(tRaw ,tRaw[Week], tRaw[ZipCode])

)

I tried that and it just returns 1 as every value