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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dhruvin
Helper III
Helper III

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

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.

1 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

10.JPG

 

Best Regards,

Lin

 

 

 

 

Community Support Team _ Lin
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
v-lili6-msft
Community Support
Community Support

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 dataBasic dataResultResult

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

 

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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

10.JPG

 

Best Regards,

Lin

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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