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 sprea...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

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