Showing # of distinct employees who were promoted, among # of overall promotions, in a chart

[Anonymous]

Hi, 

I have a clustered column chart showing the "Promotions received to date by Division". Promotions received to date is one variable, and Division is the other. I'm also trying to represent on the same visualization the number of distinct individuals within those divisions who have been promoted. For example, in Division 1, there were 22 promotions. But how many people were promoted? (Was it one person promoted 22 times, or 22 people promoted 1 time, etc.?) 

It seems that I could change the visualization type to a Line and Clustered Column Chart. However, I'm having trouble figuring out how to either use an existing variable or create a new one, to represent the distinct number of employees promoted. For reference, I have created a column in query editor for Promotion (values are Y or N). There are several other promotion-related variables in my data, including date of most recent promotion, and dates of earlier promotions. It seems like there should be a way to get the raw data I have to a measure or new variable that would show the distinct number of people promoted, but I can't figure out how to get there. 

Thanks for your help! 

[v-henryk-mstf]

Hi @Anonymous ,

Thank you very much for your feedback. Also can you share the detailed solution such as providing the relevant operation steps etc to help the other members find it more quickly.

Best Regards,
Henry

[Anonymous · Accepted solution]

I might have solved my own problem. I created a new measure, as coded below. I then added the Distinct Count measure as the line value, in my Line and Clustered Column Chart. It now shows the total number of promotions within whatever group I'm putting in the visualizations (race, gender, division, work group, etc.) as well as the number of people within that group who received promotions. 

For fun, I added a table visualization with all Employee names to the same page, and a slicer for Promotion: Y/N. It is now possible to see who within these groups has been promoted (and who has not). 

Distinct count = CALCULATE(DISTINCTCOUNT(Sheet1[Employee]),Sheet1[Promotion]="Y")