Help Request: Clustered Bar Graph - Event count on X-axis

[PhilippeP]

Hello there!

First time poster. Thank you so much in advance for the help. I mostly don't know how to ask for my solution, otherwise I'm confident I would have been able to search for my answer in the forums already.

Also I'm sorry; I can't post files or data due to the sensitive nature of what I'm working with.

I want to create a visual that shows:

• The count of HRIDs from the MM_Report table that appear in the Receiver Employee ID column of the Activity Report Data table.
• Data should only be captured for activity in the last 12 months (Activity Report Data: Received Date)
• The breakdown should be based on how many times each HRID appears in the Receiver Employee ID column.

For example:

• If an HRID appears once, it contributes to the "1" category.
• If an HRID appears 7 times, it contributes to the "7" category.
• If 7 different HRIDs appear 7 times, the "7" category should have a height of 7.

I'd love for this to respond to the slicers I have selected on the page and add some filters to the visual. I have a table which already shows the list of HRIDs, names, etc, and the count of events in the last 12 months. I would just like to have a bar graph to quickly display the spread.

If you guys could get me started that would be terrific.

Thank you!

[jgeddes · Accepted solution]

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
)

[danextian]

Hi @PhilippeP 

A sample dataset doesn't need to be confidential, but it should accurately reflect your actual data. While creating one might involve considerable effort, providing solutions can also be time-consuming. Help us help you. 

[burakkaragoz · Accepted solution]

Hi @PhilippeP ,

You can totally do this with a calculated column and a simple bar chart.

Here's how to get started:

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

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

[jgeddes · Accepted solution]

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
)