Calculate Percentage between 2 counts only if Share same Account Number

[kfortenberry]

I would appreciate any assistance with the following scenario.  I have a large table (about 11 million rows) and need to compare the counts between different "types", but only wish to compare those in the second group which share the same "account" numbers (like a left join, but it is all one table).

I only want to count the "Written" type if the account listed on the "Written" type rows also exists on a "Lead" type row... so 3 "Written" type rows divided by all "Lead" type rows which would give me 37.5%.

Account	Type
Acc123	Lead
Acc124	Lead
Acc125	Lead
Acc126	Lead
Acc127	Lead
Acc128	Lead
Acc129	Lead
Acc130	Lead
Acc123	Written
Acc124	Written
Acc128	Written
Acc132	Written
Acc133	Written
Acc134	Written

[ThxAlot]

[kfortenberry]

Thank you for the formula.  This is mostly working, but I did notice that if a duplicate account number exists in the "Written" data it is not counting the duplicates. 

Would you know of how to adjust the formula so that the duplicates would be counted?

[Anonymous · Accepted solution]

Hi @kfortenberry ,

@ThxAlot Thanks for your reply!

And @kfortenberry , here is my sample data:

And you can try this DAX:

Measure = 
VAR _Lead = 
CALCULATETABLE(
    SELECTCOLUMNS(
        FILTER(
            'Table',
            'Table'[Type] = "Lead"
        ),
        "Account", 'Table'[Account]
    )
)
VAR _Written = 
CALCULATETABLE(
    SELECTCOLUMNS(
        FILTER(
            'Table',
            'Table'[Type] = "Written"
        ),
        "Account", 'Table'[Account]
    )
)
VAR _Accounts = 
INTERSECT(_Written, _Lead)
VAR _COUNT = 
COUNTROWS(_Accounts)
VAR _COUNT_Lead = 
CALCULATE(
    COUNTROWS('Table'),
    'Table'[Type] = "Lead"
)
RETURN
_COUNT / _COUNT_Lead

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kfortenberry]

Thank you!  This worked perfectly!