How to Calculate Count by One Column with where condition of another column in Power BI?

[Dhruvin]

I have one table with following columns:

•  User ID
•  Transaction Originator
•  document Number

I want to calculate following measures:

1. Document Count by Transaction Originator =  I want Document Count by Transaction Originator where User ID = Transaction Originator.
2. Percentage = Document Count by Transaction Originator / Document Count by User ID

I want to show the Final value in the table something like:

• User ID
• Count of Document Number by User ID
• document Count by Transaction Originator
• Percentage

I want to know how can I apply where condtion with the same table's column and get the correct count.

[v-lili6-msft]

HI, @Dhruvin

You could try these formulae as below:

Count of Document Number by User ID = CALCULATE(COUNTA('Table'[document Number]))


document Count by Transaction Originator = CALCULATE(COUNTA('Table'[document Number]),FILTER('Table','Table'[User ID]='Table'[Transaction Originator]))+0


Percentage = [document Count by Transaction Originator]/[Count of Document Number by User ID]

Result:

Basic dataResult

If not your case, Please share some data sample and expected output for us. Do mask sensitive data before uploading.

Best Regards,

Lin

[Dhruvin]

Thnaks for your effort.

I really appriciate it.

I am looking for different o/p.

As per your output User ID and Document Count by User Id is correct. But for Document Count by Transaction Originator I'm looking for the output something like below:

For User ID - A001 - document Count by Transaction Originator should be = 3 instead of 2.  I need all the count of Document by Transaction Originator but only want to represent wrt UserID.

Same, UserID - A002 document Count by Transaction Originator should be = 5 instead of 3. 

I know it's bit confusing.! Hope you understand.

[v-lili6-msft · Accepted solution]

hi, @Dhruvin

You could use this formula as below:

document Count by Transaction Originator = CALCULATE(COUNTA('Table'[document Number]),ALL('Table'[User ID]),'Table'[Transaction Originator] in VALUES('Table'[User ID]))+0

Best Regards,

Lin

[Dhruvin]

@v-lili6-msft - Thanks a lot. That's what I'm looking for. Big thanks to you.