Forum Discussion
Help Request: Clustered Bar Graph - Event count on X-axis
Here is one way to do this...
Create a calculated column that counts how many times an Id is present in the table for the last 12 months.
Create the visual you want with column you just created as the dimension and the distinct count of the ids as the values.
You may need/want to create a dimension table to "fill in the blanks" of the count dimension.
With a very small sample set here is a possible result...Here is the calculated column code I used. I also attached a pbix file for you to look through.
Hope this gets you pointed in the right direction.Instances of ID = var _id = [Receiver Employee Id] RETURN CALCULATE( COUNT('Table'[Receiver Employee Id]), FILTER('Table', 'Table'[Receiver Employee Id] = _id), DATEDIFF(TODAY(), 'Table'[Received Date], MONTH) >= -12 )Hi PhilippeP ,
You can totally do this with a calculated column and a simple bar chart.
Here's how to get started:
- Add a calculated column to your table (let’s say it's called Activity Report Data) to count how many times each Receiver Employee ID appears in the last 12 months:
EventCountLast12Months = VAR _id = [Receiver Employee ID] RETURN CALCULATE( COUNT('Activity Report Data'[Receiver Employee ID]), FILTER( 'Activity Report Data', 'Activity Report Data'[Receiver Employee ID] = _id && 'Activity Report Data'[Received Date] >= TODAY() - 365 ) )- Create a bar chart
- Axis: EventCountLast12Months
- Values: Distinct count of Receiver Employee ID
This will give you a nice spread of how many HRIDs had 1, 2, 3… N events in the last year.
Optional: If you want to show all possible counts (even if some have 0), you can create a small dimension table with numbers 1–N and relate it to your data.
Let me know if you want help with that part too!
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
Translation and text editing supported by AI
