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

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.

    • kfortenberry's avatar
      kfortenberry
      Frequent Visitor
      2 years ago

      Thank you!  This worked perfectly!