Counting Instances of an ID from another table

[Scoobyrooba]

Hey All,

I have two tables. One includes headcount information, and the other includes records of classes taken by the people in the headcount table. I want to created a visual that shows what percentage of employees have completed 25%, 50%, 75%, or 100% of available classes. 

I am trying to figure out if I should do this in a column or a measure. However I am fairly new to Power BI and can;t think of a formula for either. I am thinking I would need to do it in a column so that i could then perform a count of employees that completed a certain number of classes.

Does anyone have thoughts on what the correct way to do this would be?

Thanks 

[v-lili6-msft · Accepted solution]

hi, @Scoobyrooba

First, you should know difference between measure and column, here are two blogs for you learn:

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

https://www.blue-granite.com/blog/understanding-the-differences-between-calculated-columns-measures-...

Even if they look similar, there is a big difference between calculated columns and measures. The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report. A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.

At this point, you might be wondering when to use calculated columns over measures. Sometimes either is an option, but in most situations your computation needs determine your choice.

You have to define a calculated column whenever you want to do the following:

Place the calculated results in a slicer, or see results in rows or columns in a pivot table (as opposed to the values area), or in the axes of a chart, or use the result as a filter condition in a DAX query.
Define an expression that is strictly bound to the current row. For example, Price * Quantity cannot work on an average or on a sum of the two columns.
Categorize text or numbers. For example, a range of values for a measure, a range of ages of customers, such as 0–18, 18–25, and so on.
However, you must define a measure whenever you want to display resulting calculation values that reflect user selections and see them in the values area of a pivot table, or in the plot area of a chart – for example:

When you calculate profit percentage on a certain selection of data.
When you calculate ratios of a product compared to all products but keeping the filter both by year and region.

So you still have the problem, you could share some sample data and expected output. and then I can give you more suggestion.  Do mask sensitive data before uploading.

Best Regards,

Lin