cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

1 ACCEPTED SOLUTION
Community Support

Hi @kfortenberry ,

And @kfortenberry , here is my sample data:

And you can try this DAX:

``````Measure =
CALCULATETABLE(
SELECTCOLUMNS(
FILTER(
'Table',
),
"Account", 'Table'[Account]
)
)
VAR _Written =
CALCULATETABLE(
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Type] = "Written"
),
"Account", 'Table'[Account]
)
)
VAR _Accounts =
VAR _COUNT =
COUNTROWS(_Accounts)
CALCULATE(
COUNTROWS('Table'),
)
RETURN

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 4
Super User

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Frequent Visitor

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?

Community Support

Hi @kfortenberry ,

And @kfortenberry , here is my sample data:

And you can try this DAX:

``````Measure =
CALCULATETABLE(
SELECTCOLUMNS(
FILTER(
'Table',
),
"Account", 'Table'[Account]
)
)
VAR _Written =
CALCULATETABLE(
SELECTCOLUMNS(
FILTER(
'Table',
'Table'[Type] = "Written"
),
"Account", 'Table'[Account]
)
)
VAR _Accounts =
VAR _COUNT =
COUNTROWS(_Accounts)
CALCULATE(
COUNTROWS('Table'),
)
RETURN

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.

Frequent Visitor

Thank you!  This worked perfectly!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.