Dynamic Measure based on filter

[kjsullivan]

I need to find what percent of companies that buy one product also buy another. A company can buy product H,V,D,SU,LD, and SD. However, because of this, an Account (company) will show up in the spreadsheet multiple times if they bought multiple products.  

I have a few calculations that find the distinct value of products and the total accounts, but when I go to filter by Product my measures mess up. For Example, my code for Product D is

DCount = CALCULATE (DISTINCTCOUNT (Sheet1[Account]),Sheet1[Product]="D"

This calculation works and I get the correct amount of Distinct Accounts that buy Product D. My code for Total Accounts is

Total Accounts = CALCULATE( DISTINCTCOUNT(Sheet1[Account]) )

This also works. I then have one final measure to calculate the percentage which is

% D= DIVIDE([D],[Total Accounts])

This measure works also. I have 4 Distinct Accounts so Total Accounts = 4. Only 3 of those Accounts bought D (even though there are 4 instances of it being bought). So my %D = 75%.

But, say I go to filter by Product H. Since 2 Accounts purchase product H, Total Accounts changes to 2, but DCount does not change and stays at 3. So my measure for %D = 3/2 or 150% when in reality, of the 2 companies that bought H, only 1 bought D or 50%.

I am not sure how to get my measures to filter to a Product filter or if my formulas are wrong completely. I am confused because I would have thought the filter would at least make my DCount go to 0, not just stay at 3.

Thank you!

[v-yulgu-msft · Accepted solution]

Hi @kjsullivan,

Based on my understanding, you added the [Product] into a slicer, you want the percentage value to be dynamically changed depend on the slicer selection, right?

Please modify your measures as below:

DCount =
CALCULATE (
    DISTINCTCOUNT ( Sheet1[Account] ),
    FILTER ( Sheet1, Sheet1[Product] = LASTNONBLANK ( Sheet1[Product], 1 ) )
)

Total Accounts =
CALCULATE ( DISTINCTCOUNT ( Sheet1[Account] ), ALL ( Sheet1 ) )

% D = DIVIDE([DCount],[Total Accounts])

Best regards,
Yuliana Gu

[mattbrice]

This sounds like what you are looking for:

http://www.daxpatterns.com/basket-analysis/