Add Goal line to line chart with other data

[Anonymous]

Hello,

My company has an internal goal they have set. I created a measure for this goal and wanted to add it to the monthly performance I have put together in a line chart. The goal consisits of the same percentages that start over every month. I wanted this goal line added to the other data points in the line chart. Is there a line chart that does not only use 1 value and can use 2? Or one and a measure? The original chart was created in excel and I am trying to create the equivalent.

Thank you for your help!

[Ephack]

Puting "Goal" below "Running %" is not working?

[Anonymous]

It was not! I tried! Maybe because I have the filter for month_year I am assuming.

[vivran22]

Hey @Anonymous,

Can you share the Excel graph to see what you are looking for as an output?

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Anonymous]

Hi Yes. Please take a look. I have the other lines but want to add the grey goal line and some how the powerBI line chart will not let me. 

[vivran22 · Accepted solution]

@Jporter

The regular line graph in the Power BI should solve the purpose. My advise would be to create separate measures for all the lines.

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Anonymous]

Okay that makes sense. How do I create a measure for same time last year that can automatically update? The other months I can do.

[vivran22]

@Anonymous 

You may use SAMEPERIODLASTYEAR.

CALCULATE([Measure Name], SAMEPERIODLASTYEAR(Calendar[Date]))

For more details, you may refer to the following article:

DAX Time Intelligence Part 1: Introduction (vivran.in)

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Anonymous]

Hi Vivran,

Thank you so much for your help. However, it seems to not be working compared to what I had before.

They all have to start over at 0. I created a column for Day of Month and that let it start over at 0 like the chart above.

[vivran22]

@Anonymous 

Can you share the sample data/pbix file?

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

[Anonymous]

I got the current month to work using this:

urrent_month = CALCULATE(

[Running %],

FILTER(

ALLSELECTED('Total Collected'[Day of Month]),

'Total Collected'[Day of Month]<= MAX('Total Collected'[Day of Month])

)

)

I cannot get it to work for last month or last year. I wonder if it is also because I added a date filter to the graph.

[vivran22]

@Anonymous 

Providing support without any detail of the data model is very difficult. Are you using a calendar/date table in your model? Time Intelligence functions works best on the date columns and I think that [Day of the Month] is not a date column.

[Anonymous]

Unfortunatly, I cannot share the file. I don't have any sample data. 

What I can share is this is how the Running % is calculated.

Running DQ Total =

CALCULATE (

SUM ( 'Total Collected'[total_collected] ),

FILTER(

ALLSELECTED('Total Collected'[Day of Month]),

'Total Collected'[Day of Month]<= MAX('Total Collected'[Day of Month])

)

)

Running % = DIVIDE('Total Collected'[Running DQ Total], Collections[Overall DQ $ StartOfMonth])

I am thinking I may have to do something similar for current month, last month and last year same as current month.