Using filter functions on a measure in table

[abeirne]

Hi all, I am trying to show the email capture rate of my stores, but the table I am currently using shows all of the employees. I would like to show the email capture rate per store versus per employee. I am thinking maybe an ALL() would work? I have tried all() on the main store # table and columns but did not seem to work. 

Current measure:

Email Capture % =

CALCULATE(

DISTINCTCOUNT(Invoice_[Customer_Email_Address]) / 'Invoice_Detail_'[GC],

ALL(Invoice_)

)

So for store 17, I want the email capture rate to be the same, showing the capture rate for the whole store instead of individually, and so on for the next stores. I have redacted some info. Thank you all for the help. 

[Anonymous · Accepted solution]

Hi @abeirne ,

Are 'Invoice_Detail_' [GC] the same for the same stores?

Please try this formula.

Email Capture % =
CALCULATE (
    DISTINCTCOUNT ( Invoice_[Customer_Email_Address] ),
    FILTER ( ALL ( Invoice_ ), [stores] = SELECTEDVALUE ( stores ) )
)
    / SELECTEDVALUE ( 'Invoice_Detail_'[GC] )

Best Regards,

Jay

[abeirne]

Yeah I know it was not super clear, sorry for that. I hadn't thought of the allexcept, I will try it. Thank you!

[amitchandak]

@abeirne , Not very clear. Try like

Current measure:
Email Capture % =
CALCULATE(
divide(DISTINCTCOUNT(Invoice_[Customer_Email_Address]) , 'Invoice_Detail_'[GC]),
ALLEXCEPT(Invoice_, Invoice_ [Store])
)