Sum of a Count

[bhmiller89]

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

[wonga · Accepted solution]

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"))

[KHorseman]

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])

[wonga · Accepted solution]

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"))

[MarkDGaal]

@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 · Accepted solution]

@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)