Forum Discussion

kfortenberry's avatar
kfortenberry
Frequent Visitor
2 years ago
Solved

Calculate Percentage between 2 counts only if Share same Account Number

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%.

 

AccountType
Acc123Lead
Acc124Lead
Acc125Lead
Acc126Lead
Acc127Lead
Acc128Lead
Acc129Lead
Acc130Lead
Acc123Written
Acc124Written
Acc128Written
Acc132Written
Acc133Written
Acc134Written
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

4 Replies

    • kfortenberry's avatar
      kfortenberry
      Frequent Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.