Adjustment of a Formula

[RvdHeijden]

Goodday,

I need an adjustment of a working formula

% Actief Provider A = Divide(SUM('Table'[Aantal Provider A]);(sum('Table'[Aantal Actief])))

This formula now calculates the number of subscriptions Provider A has and devides it with the total amount of subscriptions.

In this example Provider A has 2 different cariers Operator A and Operator B

So basically i need to update my formula so that it takes in a account the amount of subscriptions provider A has with Carrier A

The formula should be something like

% Actief Provider A = Divide(SUM('Table'[Aantal Provider A] with'Table' [Carrier A));(sum('Table'[Aantal Actief])))

Provider A   Carier A

Provider A   Carier B

Provider A   Carier B

Provider A   Carier A

Provider A   Carier A

[v-lili6-msft]

HI, @RvdHeijden

You could use Calculate Function to evaluate an expression in a context that is modified by the specified filters.

like:

% Actief Provider A = Divide(Calculate(SUM('Table'[Aantal Provider A]), filter('Table', 'Table' [Carrier]="Carrier")),(sum('Table'[Aantal Actief])))

For example:

result = DIVIDE(CALCULATE( SUM('Table'[Qty]),FILTER('Table','Table'[Carier]="Carier A")),SUM('Table'[Qty]))

Best Regards,

Lin

[RvdHeijden]

@v-lili6-msft

Lin, that might be a step in the right direction but we are not quite there yet.

It first has to filter in 2 different collumns in another table called 'EFOS' so basically IF a records is from Provider A and Carrier A then it should Add them up first and then devide it by the total amount of active users.

Something like this:

% Actief Provider A = Divide(calculate(SUM('Table'[Aantal Actief]);Filter('EFOS';'EFOS'[Carrier]="Carrier A";'EFOS'[provider]= Provider A)));(sum('Table'[Aantal Actief])))

This formula obviously doesnt work but its about this part in blue

[v-lili6-msft]

hi, @RvdHeijden

For two or more conditionals in the formula, you should use “&&” or AND() in it.

For example

% Actief Provider A =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Aantal Actief] ),
        FILTER (
            'EFOS',
            'EFOS'[Carrier] = "Carrier A"
                && 'EFOS'[provider] = "Provider A"
        )
    ),
    SUM ( 'Table'[Aantal Actief] )
)

or

% Actief Provider A =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Aantal Actief] ),
        FILTER (
            'EFOS',
            AND ( 'EFOS'[Carrier] = "Carrier A", 'EFOS'[provider] = "Provider A" )
        )
    ),
    SUM ( 'Table'[Aantal Actief] )
)

By the way, EFOS'[provider]= Provider A,Provider A is a text, and should be "Provider A"

Best Regards,

Lin

[RvdHeijden]

@v-lili6-msft and @ryan_mayu

In both examples the returned outcome is 100% and that is not possible because this provider has 2 carriers so it shouldn't be 100%

[v-lili6-msft]

hi, @RvdHeijden

Please check:

1. if "Table" has created a relationship with "EFOS"

2. Use ALL/ ALLEXCEPT/ ALLSELECTED in your formula like below:

(here is a blog for you as a reference:

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...  )

% Actief Provider A =
DIVIDE (
    CALCULATE (
        SUM ( 'Table'[Aantal Actief] ),
        FILTER (
            'EFOS',
            AND ( 'EFOS'[Carrier] = "Carrier A", 'EFOS'[provider] = "Provider A" )
        )
    ),
   CALCULATE (

     SUM ( 'Table'[Aantal Actief] ), ALL('EFOS'[Carrier],  'EFOS'[provider]))
)

If not your case, please share a sample pbix file or some data sample and expected output.  Do mask sensitive data before uploading.

Best Regards,

Lin

[ryan_mayu]

@RvdHeijden

% Actief Provider A = Divide(calculate(SUM('Table'[Aantal Actief]);Filter('EFOS';'EFOS'[Carrier]="Carrier A"&&'EFOS'[provider]= Provider A)));(sum('Table'[Aantal Actief])))

Please see the coding in red and try the new one.

Thanks