Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
kfortenberry
Frequent Visitor

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
1 ACCEPTED SOLUTION

Hi @kfortenberry ,

@ThxAlot Thanks for your reply!

And @kfortenberry , here is my sample data:

vjunyantmsft_0-1721717213390.png


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:

vjunyantmsft_1-1721717240363.png


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.

View solution in original post

4 REPLIES 4
ThxAlot
Super User
Super User

ThxAlot_0-1721274118926.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



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?

Hi @kfortenberry ,

@ThxAlot Thanks for your reply!

And @kfortenberry , here is my sample data:

vjunyantmsft_0-1721717213390.png


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:

vjunyantmsft_1-1721717240363.png


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.

Thank you!  This worked perfectly!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.