I dont want my distinct count to show "1" when expanding a table field....help?

[Guitarmageddon]

Hey folks. I used to use this all the time and its fallen out of memory since a lot of this is a perishable skill....

I have many vendors, across a list of buyers. Some vendors belong to just one of our buyers, sometimes the buyers share a vendor.

I have a "count distinct" to show how many distinct buyers are buying one vendor. When I expand the table out to show the buyers however, the count distinct then shows all "1" because I dont have the filter context right in the formula. For example....

This formula:
=DISTINCTCOUNT([Buyer])

Gives me this when I have all my fields collapsed,

Vendor #​	Vendor Name​	BUYER​	distinct count buyer​
1234​	VENDOR A​		3​
7654​	VENDOR C​		2​
9999​	VENDOR B​		1​
34567​	VENDOR D​		3​
Grand Total​			9​

But when I expand out the vendor column to show the constituent buyers, those distinct counts all now show "1".

Vendor #​	Vendor Name​	BUYER​	distinct count buyer​
1234​	VENDOR A​	JONES​	1​
1234​	VENDOR A​	SMITH​	1​
1234​	VENDOR A​	DOE​	1​
7654​	VENDOR C​	HAMPTON​	1​
7654​	VENDOR C​	LINCOLN​	1​
9999​	VENDOR B​	DOE​	1​
34567​	VENDOR D​	OCONNOR​	1​
34567​	VENDOR D​	WASHINGTON​	1​
34567​	VENDOR D​	WILLIAMS​	1​
Grand Total​			9​

How did I need to change the formula to ALWAYS remain showing that distinct count of buyers (within each vendor name/number, whichever you decide)? I vaguely recall some mix of ALL, CALCULATE, FILTER....and so forth but its all a fog to me now, been a while since Ive seriously commited myself to dax, and it shows.

 

[RoryA]

Not sure what you expect there. You're grouping by BUYER so the count is 1 for each of them.

 

[Guitarmageddon]

Not sure what you expect there. You're grouping by BUYER so the count is 1 for each of them.

correct, but if you look at vendor A, i want the pivot to always indicate the count of distinct buyers is 3 no matter how I choose to expand/collapse my filters. There was a way to do it with DAX but I cant remember. Just because it preserved the continuity of that filter for the particular analysis I was doing.

 

[RoryA]

Doesn't make sense to me, but you'd want something like:

=CALCULATE(DISTINCTCOUNT(Tablename[BUYER]),ALLEXCEPT(Tablename,Table2[Vendor Name]))