cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Plotting a graph of most in each category

I have a dataset that looks like this:

``````TicketID,TicketType,Department
1,Install,HR
2,Demo,IT
3,Demo,HR
4,Meeting,SALES
5,Install,IT
6,Install,IT
7,Meeting,SALES
8,Demo,HR
9,Meeting,IT
10,Meeting,SALES``````

I want to plot a graph of which the X-axis is [ Ticket Type ]. I need the Y-axis to be the number of tickets submitted by the department that submitted the most tickets in [ Ticket Type ].

For instance, based on above data, for X-value Install, I need Y-value to be something like 2 (IT), because IT department submitted two Install tickets. Similarly, Demo = 2 (HR), and Meeting= 3 (SALES). In case of ties, I'd prefer a concatenated lable, something like 3 (IT, SALES). Essentially, somethhing like this:

How could this be done?

EDIT:
I know that I can create a measure like below and plot the graph with just the values. But I don't know how the get the labels right, such as 2 (HR) instead of just 2.

``````Count of TicketID max per Department =
MAXX(
KEEPFILTERS(VALUES('Data'[Department])),
CALCULATE(DISTINCTCOUNT('Data'[TicketID]))
)``````

3 REPLIES 3
Super User

You can achieve this by adding a Calculated Column like the below and representing it on the chart as "Maximum"

`_col1 = VAR _1 = CALCULATE ( COUNTROWS ('Table' ) , ALLEXCEPT ( 'Table' ,'Table'[Department] ,'Table'[TicketType] ) )RETURN_1`

To get the graph, just use the TicketType as Axis and Max of _col1 as the values.

If you don't want to use the "maximum" of the column, you can just add a measure like the below and add it to the value field in the chart.

`Maximum of _col1 = MAX ('Table'[_col1] )`

Hope this helps!

Theo 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Helper II

As I pointed in my EDIT, I could do this part. What I'm struggling with is getting the lables the way I want.

Super User

Hi @Sachintha the EDIT came after the post and because EDITs do not appear in respondents' notification bars, we won't be notified unless you advise - so thanks for that.

There are some limitations. You can't do it to Data Labels (i.e. to stay on permanently) from what I understand but there are some workarounds such as using custom tooltips.  If something like this is satisfactory, you can go here: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-tooltips?tabs=powerbi-desktop to learn more.  I've also attached a PBIX just in case it's of any benefit.

Cheers,
Theo

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors