Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ghouse_peer
Post Patron
Post Patron

Showing values in clustered column chart

Hello Guys,

 

 I have a task where i need to show Tickets Opened and tickets Closed in Clustered column chart.

 

I have  data lik this: 1) Table Name: Report1, Columns: Ticket Opened Date(Date), Ticket closed date(Date), Ticket Number(whole Number)

2) Table Name(Created Using Dax Calendar): Dates( contains date from start to end)

 

So task is using clustered Column chart. I need to show  all 12 months in x axis and Count of tickets Opened/Closed according to month(i,e 1 column showing Tickets opened and another column showing Tickets Closed). and in Y axis units should be in thousands.

 

I tried using this for creating a seperate column : 

CALCULATE(COUNT('Report 1'[Ticket Number]),FILTER('Report 1','Report 1'[Ticket Closed Date]))
but failed. 
 
Kindly provide the solution. Thanks in advance

 

 

3 REPLIES 3
MFelix
Super User
Super User

Hi @ghouse_peer ,

 

Create a relationship between the calendar table and the Opened Date and the Closed Date. depending on the order you make it one will be active and the other inactive then just create the following measures:

 

Open Tickets = COUNT(Report1[Ticket Number])

Closed Tickets = CALCULATE ( COUNT(Report1[Ticket Number]), USERELATIONSHIP(Report1[Ticket Clodes Date],Calendar[Date] ) )

 

Now place both measure in the chart using the calendar as the X-Axis.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hello @MFelix 

 

 I have done as said, But i am getting the solution as in  the pic.

2017 data.JPG

 

2016 Data.JPG

 

After doing the above calculations:

 

1) Selected the both measures in the values.( Tried for both clustered column chart and Stacked bar chart)

2) Selected Dates table in Axis.

3) Selected Dates table in Slicer.

 

Issue 1: So when i selected the year in slicer i am getting only opened Tickets for 2017. Whereas i have data for all the years for both Opened and closed Tickets.

 

Issue 2: I need to get all 12 months on the axis.

 

Kindly Help...

 

Thank you

Hi @ghouse_peer ,

 

Can you share the measure formula and an image of your relationships?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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