Struggling - New to power bi need to create a line and clustered column chart

[P0W3RB1]

Hi

Only just starting to use Power BI and i am trying to create a "line and clustered column chart" and getting nowhere. I want to display 3 values, two of which use the same value but filtered differently, i cant for the life of me get both to work only one or the other. Can you have a clustered bar chart that uses the same value but split based on a "visual level filter" ? the 3rd value i want to display is a sales target, but when i put the field into "line values" i just get a big straight line, if i do a line graph on its own it shows the correct data.

so confused, any help?

[Beckham]

I would create two measures that each return the result you're looking for. Then you can bring them on the chart separately:

For example if you want to see sales by California and New York, and see two states in the bar chart:

Measure 1 : Sum(Sales[Sales Total])

Measure 2: Calculate([Measure 1],Sales[State] = "California")

Measure 3: Calculate([Measure 1],Sales[State] = "New York")

Does that help?

[P0W3RB1]

Yeah that kinda makes sense, i did actually click on that but didnt know what i was doing.

As for the line graph, which is unrelated to the other two amounts why would that just be a flat/straight line? its just totalled up the figure for the whole year rather than split it down by month?? why would it do that when the other date is split per month?

[Beckham]

I would check out some trainings and webinars. A little time there can get you moving pretty quickly. 

Another option you may have instead of writing measures would be to bring the thing you want to be in different bars (state for example) onto the legend. 

As for the line graph issue, i'd check out your data. It may not be at the same granularity as the other calculations. Can you give me an example of what it looks like?

[P0W3RB1]

sure it looks like this, the line is just straight across at 9 million which is the total for the whole year, for some reason it just doesnt wnant to split down by month. The odd thing is that if i make a new line chart, with just that data i want to display it works! it just doesnt want to play nicely with the other data!

[KHorseman]

What is the formula for the measure that you're using as the line? Is it based on the same date field that the other is using?

[P0W3RB1]

No it isnt! if i use the date field that the line is using, the line is correct but the columns are wrong, and vice versa.

I need to somehow make them use the same month, combine the two date fields somehow

is that even possible?

[Eric_Zhang]

---

@P0W3RB1 wrote:

No it isnt! if i use the date field that the line is using, the line is correct but the columns are wrong, and vice versa.

I need to somehow make them use the same month, combine the two date fields somehow

is that even possible?

---

@P0W3RB1

So the column values and the line values are from two different tables with no any relationship? If so, try to create a calendar table and create proper relationship. In the visualization, use the date field from calendar as the shared axis. See a demo in the attached pbix.

If you have any question, feel free to let me know.

[P0W3RB1]

Thank You! this is making more sense to me now! i know where to create this new table, could you possibly give me an example of the command i type in order to get this table?

lets say one date field is called "Date1" and the other is called "Date2" what command would i type?

[Eric_Zhang]

@P0W3RB1

You can find the calendar table expression when clicking on it in the attached pbix.

CalendarTable = CALENDAR(IF(MIN(ColumnTable[DT])<MIN(LineTable[DT]),MIN(ColumnTable[DT]),MIN(LineTable[DT])),IF(MAX(ColumnTable[DT])>MAX(LineTable[DT]),MAX(ColumnTable[DT]),MAX(LineTable[DT])))

Just replace the ColumnTable[DT], LineTable[DT] accordingly with your "Date1" and "Date2".

[P0W3RB1]

OK so just so im clear here, your example of (ColumnTable[DT]) i replace the "column table" bit with the real name of my table, and the [DT] bit i replace with the field name containing my date is that correct?

Thanks.

[P0W3RB1]

OK i have progress, sort of. Now the column and the line seem to be using the date from the new table created, but the figures arent making sense. For example the black line is for sales targets, but theres no data beyopnd february and there defenitely is. Could that be the table relationship i have done wrong?

[sdjensen]

Could you try to use another visual just for testing? There has been reported some bugs with the combined bar and line chart not showing all data.

[P0W3RB1]

Hi, it looks like this right now. Actually the line figures for Jan and Feb are correct, but wheres the rest gone?

I tried making a line graph on its own and get the same results there. If i look at the data in the new table i created i can see dates for April 2016 so not sure why its not showing?

[Eric_Zhang]

@P0W3RB1

---

@P0W3RB1 wrote:

Hi, it looks like this right now. Actually the line figures for Jan and Feb are correct, but wheres the rest gone?

I tried making a line graph on its own and get the same results there. If i look at the data in the new table i created i can see dates for April 2016 so not sure why its not showing?

---

Can you post more details, like the expression of your measures and the relationship, or even better uploading an test pbix? We can't figure out why the problem happens by guessing.

[P0W3RB1]

Think im close to having this working now, how do i change this DAX script to include a 3rd table, in order to create my master calendar table?

CalendarTable = CALENDAR(IF(MIN(ServiceAppointmentSet[ScheduledStart])<MIN(ins_SalesTargetSet[ins_Month]),MIN(ServiceAppointmentSet[ScheduledStart]),MIN(ins_SalesTargetSet[ins_Month])),IF(MAX(ServiceAppointmentSet[ScheduledStart])>MAX(ins_SalesTargetSet[ins_Month]),MAX(ServiceAppointmentSet[ScheduledStart]),MAX(ins_SalesTargetSet[ins_Month])))

 

[Eric_Zhang]

---

@P0W3RB1 wrote:

Think im close to having this working now, how do i change this DAX script to include a 3rd table, in order to create my master calendar table?

CalendarTable = CALENDAR(IF(MIN(ServiceAppointmentSet[ScheduledStart])<MIN(ins_SalesTargetSet[ins_Month]),MIN(ServiceAppointmentSet[ScheduledStart]),MIN(ins_SalesTargetSet[ins_Month])),IF(MAX(ServiceAppointmentSet[ScheduledStart])>MAX(ins_SalesTargetSet[ins_Month]),MAX(ServiceAppointmentSet[ScheduledStart]),MAX(ins_SalesTargetSet[ins_Month])))

 

---

A what 3rd table and why? The CALENDAR function accepts two parameters, and it can be as simple as below to get a table covering all the dates of year 2016. The quoted calendarTable just covers the all the dates involved in your two tables.

CALENDAR("2016-01-01","2016-12-31")

[P0W3RB1]

OK. I need to create a calendar table using a DATE field from 3 seperate tables. I thought i would start with 2 just to get the hang of things, and it worked! but now i need a 3rd date and since learnt that the DAX syntax someone provided me with only excepts two tables. Surely there must be a way of including a 3rd table? otherwise what is the point in having a clustered bar and line chart?

the tables are:

Invoiced figures

order amount figures

sales target figures.

i wanted the bars to show me the invoiced and ordered amounts, which works fine thanks to the syntax someone here provided me with! But now i want the line on top of the bars to display the sales target on top of that. Sales target is in a seperate table.

Unless all 3 date fields exist in the same table, the clustered bar and line charts dont work at all. So according to what i have read  i need to create a calendar table and use the date field from the new calendar table to make it work. But how do i take into account a 3rd table???

I think that clustered bar and line chart is useless unless all 3 of the dates you want to use are in the same table.

[Eric_Zhang]

@P0W3RB1

It might be due to the relationship. Can you upload a sample problematic pbix? You can use any free web storage and do remember to mask sensitive data in your dataset.

[kcantor]

It absolutely is possible. You need to add a date dimension table and relate the measures through it based upon the date within the sales fact table. Then, use the months from the date column as  your axis.  Check out this post:

http://exceleratorbi.com.au/power-pivot-calendar-tables/