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

Guitarmageddon

Board Regular
Joined
Dec 22, 2014
Messages
161
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Not sure what you expect there. You're grouping by BUYER so the count is 1 for each of them.
 
Upvote 0
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.
 
Upvote 0
Doesn't make sense to me, but you'd want something like:

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

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top