DAX Calculate Measure unaffected by row in table

Question

I'm new in DAX and I'm having some trouble calculating a value that must be similar in all the rows independently of the row. I'll explain in more detail:

I have to calculate the adoption of certain use of a program:

Sales in Program/Total Sales

(This only includes customers that have access to the program. Total sales might include sales performed by the program or not)

Some customer are not onboarded in this program and I would like to calculate the impact of onboarding this customer into the program:

 # of Sales in Program/# of Total Sales - # of Sales in Program/(# of Total Sales + # of Customer sales)

The # of customer sales is of course outside the program. The adoption calculation (# of Sales in Program/# of Total Sales) must be the same for all customers, the only data that changes according to the row is # of Customer sales. My data is transactional and is like this:

Customer Order    Date        Onboarded  Program
01       001      01-01-2018  Yes        In Program
01       002      01-01-2018  Yes        Not In Program
03       003      01-01-2018  No         In Program

So far, I have:

Adoption = CALCULATE(  COUNT(Adoption[Order]), 
                       FILTER(Adoption, Adoption[Program] = "In Program" &&
                                        Adoption[Onboarded] = "Yes")
            /
            CALCULATE(  COUNT(Adoption[Order])
                       FILTER(Adoption, Adoption[Onboarded] = "Yes"))

I tried to use All but still it was affected by the rows. How can I make it so that the adoption is the same for all the rows?

So far, my output is something like this (note: right now I'm just calculating the first part of the equation):

Customer  Adoption
01        0.5
02        0.2
03        0.7

However, it should be the same for all:

Customer  Adoption
01        0.4
02        0.4
03        0.4

Thanks in advance

Can you show us what values you are getting and what values you expect to get? — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2018-12-06T03:23:52

I can't duplicate your values and your Adoption measure appears to be missing a ) and a , at the end of the 3rd and 5th lines respectively.

However, if you want to use ALL here, then it needs to go inside the FILTER function like this:

Adoption =
CALCULATE (
    COUNT ( Adoption[Order] ),
    FILTER (
        ALL ( Adoption ),
        Adoption[Program] = "In Program"
            && Adoption[Onboarded] = "Yes"
    )
)
    / CALCULATE (
        COUNT ( Adoption[Order] ),
        FILTER (
            ALL ( Adoption ),
            Adoption[Onboarded] = "Yes"
        )
    )

The reason it doesn't work like this

CALCULATE(COUNT(...), ALL(Adoption), FILTER(Adoption, ...))

is that the arguments of CALCULATE are combined using AND logic and the FILTER function is already a proper subset of ALL(Adoption) since the table passed as the first argument is already evaluated within the local filter context. That is, the Customer number is a filter on that table unless you remove it. In order to remove that filter context, you need to apply ALL at that level instead of trying to combine it after you've done your filtering.

DAX Calculate Measure unaffected by row in table

1 Answers