how to create Average line in Line and stacked column chart

[ryan_mayu]

Hi all,

Below is my sample data. I want to create a line and stacked column chart. I want to show the amount of the latest line as a line or the average of months' value I selected as a line.

For example,

show the latest amount as a line - if I choose Jan, Feb, Mar 2018, I will have three bars with 100,200,300 and a straight line (value as 300)

show the average amount as a line - if I choose Jan, Feb, Mar and Apr 2018, I will have four bars with 100,200,300,400 and a straight line for all four months (value = (100+200+300=400)/4)

That means the line's value shoud be dynamic becuase that depends on how many months I choose.

How can I create those two measures?

Thanks in advance.

j

[v-juanli-msft · Accepted solution]

Hi @ryan_mayu

It seems a mistake for your first example.

if I choose Jan, Feb, Mar 2018, I will have three bars with 100,200,300 and a straight line (value as 300)

It should be 200=(100+200+300)/3

You could add a average line in a stacked column chart 

Or create a measure in a line and stacked column chart,

Measure = CALCULATE(AVERAGE(Sheet1[sales]),ALLSELECTED(Sheet1[Month]))

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @ryan_mayu

It seems a mistake for your first example.

if I choose Jan, Feb, Mar 2018, I will have three bars with 100,200,300 and a straight line (value as 300)

It should be 200=(100+200+300)/3

You could add a average line in a stacked column chart 

Or create a measure in a line and stacked column chart,

Measure = CALCULATE(AVERAGE(Sheet1[sales]),ALLSELECTED(Sheet1[Month]))

Best Regards

Maggie

[ryan_mayu]

@v-juanli-msft

Thanks for your help. I am sorry that I didn't explain my request clearly.

Now I have two tables. Fact table has date and amount. Date table has date and month name.

Two tables have been linked by date column.

Now I want to drag out the month column and set it as a filter. I tried your solution, but it does not work for this scenario.

Hope you can help me again. Thanks in advance.

[v-juanli-msft]

Hi @ryan_mayu

I create calcualted columns in the date table

month_number = MONTH('calendar'[Date])

year_number = YEAR('calendar'[Date])

Create relationship between date table and your table

then add "date" from this date table in the Axis, add columns from the date table as slicers

Best Regards

Maggie

[ryan_mayu]

@v-juanli-msft

Thanks for your help. Could you please take a look at my data. I don't know why I can't get the same average value. Meanwhile, could you please let me know how to upload the PBIX file if you need that?

[v-juanli-msft]

Hi @ryan_mayu

Try this measure

sum = CALCULATE(SUM(Sheet1[sales]),ALLSELECTED('calendar'))

month_count = CALCULATE(DISTINCTCOUNT('calendar'[month_number]),ALLSELECTED('calendar'))

average = IF(MAX(Sheet1[sales])<>BLANK(),[sum]/[month_count]) or
average = [sum]/[month_count]

To upload a pbix, you could Upload files to OneDrive, then share the link here.

Please confirm your uploaded file doesn't contain private information.

Best Regards

Maggie

[ryan_mayu]

@v-juanli-msft

Thanks for your help. I am sorry I can't open onedrive maybe becuase I am in China.

My sample data is very simple.

Fact table

data table

I used your solution. The data is fine if I show all the months. However, if I select month, I encountered an error.

Any idea about this?

Thanks

[v-juanli-msft]

Hi @ryan_mayu

I use your date to make a test

please refer to my pbix

Best regards

maggie

[v-juanli-msft · Accepted solution]

Hi @ryan_mayu

I think your date table is different from mine.

Based on my experience, it is not very good to have a date table like yours, it will be limited in many scenarios.

I will explain more details for my solution.

1.create a date table (you can download my file in the last post,  and have a look at the table "calendar")

click on new table, enter the code

calendar = CALENDAR(DATE(2018,1,1),DATE(2019,12,31))

Then in this table, create calculated columns

month_number = MONTH('calendar'[Date])

year_number = YEAR('calendar'[Date])

Then create a relationship between the "calendar" table and your table based on "date" column.

2.create measures in the data table "sheet5"

sum = CALCULATE(SUM(Sheet1[sales]),ALLSELECTED('calendar'))

month_count = CALCULATE(DISTINCTCOUNT('calendar'[month_number]),ALLSELECTED('calendar'))

average = IF(MAX(Sheet1[sales])<>BLANK(),[sum]/[month_count]) or
average = [sum]/[month_count]

add column "year_number" and "month_number" in the slicers.

Best Regards

Maggie

[ryan_mayu]

@v-juanli-msft

Thanks for your suggestions. I will improve my date table.