topic Trying to understand how to filter and divide two values in the same column. in DAX Commands and Tips

Trying to understand how to filter and divide two values in the same column.

aeinc01 — Sun, 11 Dec 2022 10:12:49 GMT

Hi,

I'm new to Power Bi and I'm currently running into a issue with wanting to calculate fullfilment rate. This is the function I've come up with, but it's currently running an error.

Completion Rate =
DIVIDE(
Calculate COUNT('Table13'[name]), FILTER('Table13','Table1'[AccountStatus]="Open")),
Calculate COUNT('Table13'[name]) , FILTER('Table13','Table1'[AccountStatus]="Closed")))*100)

Name	AccountStatus
John York	Open
Sarah Black	Open
Megan Birch	Open
George Howard	Closed
Frank Sacks	Closed

Re: Trying to understand how to filter and divide two values in the same column.

Nathaniel_C — Sun, 11 Dec 2022 00:15:18 GMT

Hi aeinc01 ,
You're showing one table. What is table 13? I am thinking that is your error.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: Trying to understand how to filter and divide two values in the same column.

aeinc01 — Sun, 11 Dec 2022 10:12:27 GMT

Table13 is the name of the mock table posted. I accidently typed a type. Table 1 is also Table 13.

Re: Trying to understand how to filter and divide two values in the same column.

Nathaniel_C — Sun, 11 Dec 2022 17:05:47 GMT

Hi aeinc01 ,
I think this is what you are trying to do ("//" comments out the line):

Completion Rate = DIVIDE ( //Calculate( COUNT(Table13[name]), FILTER('Table13','Table13'[AccountStatus]="Open")), //Calculate COUNT('Table13'[name]) , FILTER('Table13','Table1'[AccountStatus]="Closed")))*100)) CALCULATE ( COUNT ( Table13[Name] ), FILTER ( Table13, Table13[AccountStatus] = "Open" ) ), CALCULATE ( COUNT ( Table13[Name] ), FILTER ( Table13, Table13[AccountStatus] = "Closed" ) ) )

but doesn't this make more sense?

Completion Rate 2 = DIVIDE ( CALCULATE ( COUNT ( Table13[Name] ), FILTER ( Table13, Table13[AccountStatus] = "Closed" ) ), CALCULATE ( COUNT ( Table13[Name] )) )

You can format it as a percentage as shown above, when you select the measure on the right.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: Trying to understand how to filter and divide two values in the same column.

aeinc01 — Sun, 11 Dec 2022 22:31:14 GMT

Thank you this works well!

Re: Trying to understand how to filter and divide two values in the same column.

Nathaniel_C — Mon, 12 Dec 2022 01:21:25 GMT

Hi aeinc01 ,

You are very welcome! Thank you for marking this as a solution.

Nathaniel