cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bhmiller89
Helper V
Helper V

Sum of a Count

I have a column called "LeadOrigin" that has three options: Prospect, Existing Client, and Inactive Client.

 

I want to calculate the sum of how many of each there are and put them in a bar chart

 

Number of Leads that were Prospects, Number of Leads that were Exiisting Clients, etc...

2 ACCEPTED SOLUTIONS
wonga
Continued Contributor
Continued Contributor

You would need to create measures for each of these. Right click the table you wish to store the measure in and the formula should be something like the following (rinse and repeat for the other two). This formula is similar to a SQL like statement where you are filtering down to only LeadOrigin = "Prospect" and getting a count of how many "Prospect's" there are.

 

Let me know if this works for you, thanks.

 

CountProspects = 

CALCULATE(COUNT(TABLENAME[LeadOrigin]), FILTER(TABLENAME[LeadOrigin] = "Prospect"))

View solution in original post

wonga
Continued Contributor
Continued Contributor

@MarkDGaal In that case, using a slicer or some filtering visual would be best. Adding the count of the "LeadOrigin" column as a "value" column in a visual and then use a slicer (which now has a searchable feature) to filter down to your specific "LeadOrigin" type or types.

 

Measures are usually for specific values. If you have 300 distinct values in a column, like you say it's not feasible to create 300 distinct measures.

 

EDIT: You would probably need to make one measure that counts the number of LeadOrigin entries (since LeadOrigins isn't aggregatable; it's probably a string)

View solution in original post

4 REPLIES 4
KHorseman
Community Champion
Community Champion

You should just be able to do a count of your table's unique identifier column and use the LeadOrigin column as either the axis value or the legend in a bar chart, depending on how you want it displayed. Legend if you're charting these against a date or something like that.

 

# of Leads = DISTINCTCOUNT(TableName[Lead_ID_Column])





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




wonga
Continued Contributor
Continued Contributor

You would need to create measures for each of these. Right click the table you wish to store the measure in and the formula should be something like the following (rinse and repeat for the other two). This formula is similar to a SQL like statement where you are filtering down to only LeadOrigin = "Prospect" and getting a count of how many "Prospect's" there are.

 

Let me know if this works for you, thanks.

 

CountProspects = 

CALCULATE(COUNT(TABLENAME[LeadOrigin]), FILTER(TABLENAME[LeadOrigin] = "Prospect"))

View solution in original post

@wonga I've been wondering something about this for a while and I figured I'd ask, let say the OP had more than 3 possible Values in his [LeadOrigin] column.... say 300. 

 

Is there any practical way to use calculate, filter, and values to save someone from having to create 300 measures?

wonga
Continued Contributor
Continued Contributor

@MarkDGaal In that case, using a slicer or some filtering visual would be best. Adding the count of the "LeadOrigin" column as a "value" column in a visual and then use a slicer (which now has a searchable feature) to filter down to your specific "LeadOrigin" type or types.

 

Measures are usually for specific values. If you have 300 distinct values in a column, like you say it's not feasible to create 300 distinct measures.

 

EDIT: You would probably need to make one measure that counts the number of LeadOrigin entries (since LeadOrigins isn't aggregatable; it's probably a string)

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors