Forum Discussion

kjsullivan's avatar
kjsullivan
Frequent Visitor
9 years ago
Solved

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.  

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!

  • 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

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee

    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