Customize legends in a bar chart

[ajdm2007]

Hey everyone,

I’ve got this bar chart:

I need to tweak a couple of things with this chart:

1. Can we make the bars thicker? They get narrower when I resize the chart because it’s responsive.

2. How do I move each legend under the bars? I need the legend to show up in Power BI.

Unless I'm grabbing the wrong visualization. 

Thanks a bunch!

Have an awesome weekend!

[v-junyant-msft]

Hi @ajdm2007 ,

May I ask if you have resolved your issue, I think lbendlin's response is quite correct. If you have solved it, please accept the replies as solutions that you find helpful so that other users can find a solution faster, thanks!

Best Regards,
Dino Tao

[lbendlin]

Please provide an example of your desired output. Note the difference between clustered and stacked column charts.

[ajdm2007]

@lbendlin Thank you for your reply.

I would like to have something like this:

Thank you again.

[lbendlin]

see attached

[ajdm2007]

Hello @lbendlin 

Thank you very much, but I'm not sure what you did.

In my case, each bar represents a different metric.

I really appreciate that you did this for me, but I’d like to understand what properties you used to group the bars and how you positioned the legend below each one.

I tried placing the metrics on the x-axis, but I can’t seem to make it work.

Is it possible that I'm using an inappropriate visualization?

Thanks again.

[lbendlin]

Yeah, don't do that.  You will want to let go of the Excel style thinking. Unpivot your data, use sort columns.  Make your data work the Power BI way.

Have a look at the file I attached.

[ajdm2007]

Hello @lbendlin 

I think I understand what you're trying to say — basically, you put the metrics in a table.

So, are you suggesting that I create these columns in my data?

I'm sorry, but I'm just trying to figure out exactly what you're suggesting I do.

Thank you for your time.

[lbendlin]

Yes, reformat your data to fit the Power BI philosophy.  When coming from Excel where tons of columns are used you need to unlearn that and unpivot your data into key/Value pairs instead.  You can still have your pivots, but these are done in the visuals, not in the raw data.

[ajdm2007]

@lbendlin 

For a better understanding, do you happen to have a youtube link which explain how to approach this.

Thank you gent,

[lbendlin]

Have a look at the file I attached. Study that. I don't have the skills to produce youtube videos.

[ajdm2007]

Hello @lbendlin 

I have created a column in my data table named `days_open`, and here is the Power Query formula I used:

 Table.AddColumn(#"Removed Columns2", "days_open", each if [status] <> ">Completed*" and [status] <> ">Completed (No Email)" and [status] <> ">Closed" and [status] <> ">Closed(Survey)*"  and [status] <> ">Autoclosed" and [sub_type] <> "Zoom" then
            if Duration.Days(Date.From(DateTime.LocalNow()) - Date.From([date_entered])) <= 7 then "0-7" 
            else if Duration.Days(Date.From(DateTime.LocalNow()) - Date.From([date_entered])) > 7 and Duration.Days(Date.From(DateTime.LocalNow()) - Date.From([date_entered])) <= 14 then "8-14" 
            else if Duration.Days(Date.From(DateTime.LocalNow()) - Date.From([date_entered])) > 14 and Duration.Days(Date.From(DateTime.LocalNow()) - Date.From([date_entered])) <= 21 then "15-21"
            else if Duration.Days(Date.From(DateTime.LocalNow()) - Date.From([date_entered])) > 21 and Duration.Days(Date.From(DateTime.LocalNow()) - Date.From([date_entered])) <= 28 then "22-28"
            else if Duration.Days(Date.From(DateTime.LocalNow()) - Date.From([date_entered])) > 28 then "+ 28 days"
            else "N/A"
        else "Completed")

I have used a bar chart visualization to show how many tickets are open for each time range. However, for some reason, Power BI is not showing any info,  even having tickets inside of the brackets. It's just show the completed ones.

Month: 08

Any idea why this might be happening?

Thank you so much, 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[ajdm2007]

Hello @lbendlin ,

Thanks a lot for sharing those articles with me; they were really helpful.

I'll apply the recommendations you gave.

Here’s the link to the sample data.

PowerBISample

Data

Basically, what I’m aiming for is a bar chart showing the tickets that haven’t been closed yet.

These tickets are divided into day ranges.

Something like this:

It’s just like what you sent me in your file.

Thank you again for your time and especially for your willingness to help me.

[lbendlin]

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\sample.xlsx"), null, true),
    tickets_Sheet = Source{[Item="tickets",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(tickets_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date_entered", type datetime}, {"date_responded_utc", type datetime}, {"date_resplan_utc", type datetime}, {"date_resolved", type datetime}, {"id", type text}, {"status", type text}, {"urgency", type text}, {"resolved_by", type text}, {"contact_name", type text}, {"summary", type text}, {"type", type text}, {"sub_type", type text}, {"source", type text}, {"time_to_response", type number}, {"time_to_resolution", type number}, {"same_day_closed", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "days_open", each if List.Contains({">Completed*",">Completed (No Email)",">Closed",">Closed(Survey)",">Autoclosed"},[status]) or [sub_type]="Zoom" then "Completed"
else 
   let dd=Duration.Days(Date.From(DateTime.LocalNow()) - Date.From([date_entered]))
   in 
      if dd <=7 then "0-7" else 
      if dd <=14 then "8-14" else 
      if dd <=21 then "15-21" else 
      if dd <=28 then "22-28" else "28+")
in
    #"Added Custom"

[ajdm2007]

Hey @lbendlin ,

Thanks again!

I’ve gone over what you sent me, and I have it exactly the same, but I can’t figure out why it’s just not working in my data model.

Here’s what I’m seeing:

I selected the month of August, where I’ve got several tickets in the 22-28 day range and several in the 28+ range, but only the completed ones show up.

But when I turn off the interactions, it displays as expected.

Digging a bit deeper, I noticed that the month filter in my report is using the resolution date (date_resolved), but since the tickets in question are still open, that date field (date_entered) is blank.

That’s the root of the issue.

You could say changing the date filter fixes the problem, but not exactly, because doing so affects the other visuals, and their values change.

Any ideas on how I can solve this?

Thanks again!

[lbendlin]

 the month filter in my report is using the resolution date (date_resolved), but since the tickets in question are still open, that date field (date_entered) is blank

How can the date_entered ever be blank?

[ajdm2007]

Hello @lbendlin 

Sorry, my mistake.

The field I am referring to that is empty is the date_resolved, actually you can confirm it in the example data I sent you.

Thank you,

[lbendlin]

the usual approach would be something like 

COALESCE([date_resolved],TODAY())

depending on what you actually want your story to be.

[ajdm2007]

Hello @lbendlin 

Using this formula, I created a calculated column in my data model called days_for_analysis. The reason for this is that the COALESCE function helps me include tickets that haven’t been resolved yet, treating them as if they were resolved today. The best part is that when I change the main filter for the months, the values in the other visuals stay consistent.

Now, let’s talk about the open tickets. After adding the column and checking August, I’ve got several open tickets.

Check out the table:

So, I created a new bar chart where I put days_open on the X-axis and count_id on the Y-axis, but it's still only showing me one bar for the completed tickets and not including the others. I need a bar for those between 22 and 28 days and another for those over 28 days.

Basically, the goal of this chart is to show the user looking at the report how many tickets we have open for the month they’re checking.

I feel like I’m getting closer to solving the issue, and it’s all thanks to your guidance. What do you think?

What I want to show in this project is the performance of my help desk team. I did one before, but it was super basic and has like 6 pages; now I want to create something more professional and managerial.

Thanks for the time you’ve spent helping me out.

I hope to keep counting on your support!

[THuận]

me too