Trouble with Gauge Filtering (Percentage of Total Count)

[Anonymous]

Context:

I'm trying to create a report for all help-desk tickets for the current year. I'm pulling the data from a SQL database where each ticket is a new column in the table. To get the total number of tickets created, I'm simply counting the columns.

On this page of my report I'd like to be able to see how many tickets each help-desk agent has completed. You can see this illustrated in my screenshots below with the stacked column chart. Next, what I would like is to have the gauge visual show the percentage of tickets (of the grand total) that each help-desk agent completed. Ex: Agent-A did 765 tickets out of 2969 so, 25%.

Problem:

My problem is the gauge visual keeps filtering it's max value. I can never get it to show what I want.

In this photo we can see our total of 2969 tickets.

However, when clicking on an agents portion, the entire gauge visual filters its max value to only count the filtered tickets.

I looked around the web and these forums for some help and managed to get it partially working using the follwing two measures, forgive the naming of the measures, I'm just testing and so wasn't really preocupied with the names:

1) m_total_tickets = CALCULATE(Count(WorkItem[Id]))

2) m_total_incidents = CALCULATE([m_total_tickets],ALL(WorkItem))

I use m_total_incidents as my gauge max value. This will sort of give me what I want. The problem here is this gives me the total tickets of all time (7184) and not just the ones from this year like I want.

So then I tried filtering the value in my measure like this:

m_total_incidents = CALCULATE([m_total_tickets],ALL(WorkItem),FILTER('WorkItem','WorkItem'[Created]>=2021-09-01))

This brings my gauge back to how it was originally with the gauge filtering its max value to match the agent I'm selecting.

I'm scratching my head and really not sure what my next move is in trying to make this work. Wondering if anyone more familiar with Power BI is able to lend a quick hand or help out with any ideas.

Thank you.

[AndrewDavies437]

You might want to try going to "guage axis" and clicking the "fx" button next to it. Here you can set the max value dynamically 🙂 

I believe there are CALENDARYEAR date functions so you could create a measure that counts a person's "max" value based on the current year and then insert it using the fx button. 

Sorry if this is confusing or not what you wanted. Good luck!