Combination chart showing average rather than actual data values with combined dates

[DataGeo]

I've got 2 different excel sheets of data within the same time period, but one table is simply by year while the other has specific days within these years. Different date formats basically.

I am trying to show this data grouped by year, but want to overlay a line showing the more granular detail of its data set, however it's only showing up as a straight averaged line rather than its actualy values.

What I have:

 

What I want overlaid

Example of the two data tables:

I have related them by the date columns but this doesn't seem to have worked.

[Ashish_Mathur · Accepted solution]

Hi,

Share the link from where i can download your workbook.

[Ashish_Mathur]

Hi,

What do the stack in the column chart represent?  What does the line depict?

[DataGeo]

Hi Ashish,

The stacked bars represent the donation amounts from the table, the line should represent the price in the other table. In the shown visualization of the combination the line is 'summed' and only showing a single value over the date range.

[Ashish_Mathur · Accepted solution]

Hi,

Share the link from where i can download your workbook.

[DataGeo]

Here you go:

https://drive.google.com/open?id=1CkyMMc6JpbV3OT8m3U6nX5iw8ydM5_Mw

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution from here.

Hope this helps.

[DataGeo]

Hi Ashish, 

Really appreciate your help with this. I am trying to recreate your solution with my original workbook so I can learn the techniques here. I have been able to follow most of the DAX coding but am stuck now on one step. It seems you created a new "Date" column on the donation table to match the date format of the calendar table.

However when I use the code in your PowerBI file it is giving me an error, see image:

Any idea what I'm doing wrong here? So close!!

[Ashish_Mathur]

Hi,

You are welcome.  Ensure that the year column is a numeric column.  If it still does not help, then share the download link of the file with your formulas.  I will try to identify and correct for the error.

[DataGeo]

Hi Ashish,

I tried changing the column to a date column exactly like how it is in your solution but could not get rid of this error. I don't understand why in your solution it works but in mine it is always giving an error.

You will see it in the data table titled "Donations". 

Thanks again for your help with this.

Here is the file: https://drive.google.com/open?id=1y1MQ7qbbpy2ja0q2lXE9rg0wGxlpKE5O

[Ashish_Mathur]

Hi,

That is happening because there are blanks in the Year column of the donations table.  Fill in those blank cells.  Else, you may also try this calculated column formula

=if(ISBLANK(Donations[Year]),DATE(2013,1,1),1*("1/1/"&Donations[Year]))

Hope this helps.

[DataGeo]

Yes! That's it. Wow the error is not very descriptive in telling you about the blank columns. Thanks so much for all your help Ashish you're the best!

[Ashish_Mathur]

You are welcome.  Glad to help.