cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kjsullivan Frequent Visitor
Frequent Visitor

Dynamic Measure based on filter

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.  

Capturev.PNG

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic Measure based on filter

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])

2.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
mattbrice Senior Member
Senior Member

Re: Dynamic Measure based on filter

This sounds like what you are looking for:

 

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

Community Support Team
Community Support Team

Re: Dynamic Measure based on filter

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])

2.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.