cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dhruvin Member
Member

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

Accepted Solutions
Community Support Team
Community Support Team

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

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.
4 REPLIES 4
Community Support Team
Community Support Team

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

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:

3.JPGBasic data4.JPGResult

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.
Dhruvin Member
Member

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

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.

Community Support Team
Community Support Team

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

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.
Dhruvin Member
Member

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

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 318 members 3,265 guests
Please welcome our newest community members: