Help getting target measure to show in stacked column line chart

[Anonymous]

Hi All,

I've created a dashboard based on Dynamics Online. The base data is the number of opportunities won by month. I've got this showing correctly by using the count of opportunityid as the Y axis and an appropriate date field as the X axis in date hierarchy mode showing just month and year out of the Opportunitie table. The issue is the Monthly target is currently sitting in a caculated column in the goals table. Its a calculated column as each goal is actually an annual target so I just divided it by 12 to get the monthly target in a calculated column. When I add that monthly target to the stacked column and line chart as the line value nothing shows...

Any advice/guidance would be greatly appreciated!

Suzanne

[Anonymous]

Please find the pbix file here: PBIX sample file 

I couldnt quite get the date table to work so I created a new table based on measures. I've been able to create a row per month that holds a measure for that months sales and target. 

The issue I have now is getting the sales measure to get the right information out of the opportunities table. If an opportunity has a entry in the fields gen_termsagreedactualuncondate and _gen_job_value then that constitutes a sale, just need a count of sales for each month for the region Rotorua/Taupo (linked regions table included).

At the top of the pbix file I have a table that has the correct data.

Below it is the stacked column and line chart that I am trying to display the data in.

Thankyou for your help so far! I appreciate the assitance and look forward to being able to help others with this knowledge...

Suzanne

[Anonymous]

ok, I believe I finally got this sorted! The formula I used is:

JanuarySales = COUNTX(FILTER(opportunities,RELATED(gen_regions[gen_name])="Rotorua/Taupo" && opportunities[gen_termsagreedactualuncondate].[Year]=2021 && opportunities[gen_termsagreedactualuncondate].[Month] = "January"), opportunities[gen_termsagreedactualuncondate])

Which gave the right numbers when I update the month 🙂

Thanks for all your help and suggestions

[V-pazhen-msft]

@Anonymous 
That sounds strange, you should able to create the chart if relationship are managed properly.Can you create a sample pbix removing or changing sensitive data. 

Paul Zheng _ Community Support Team

[jdbuchanan71]

Hi @Anonymous 

You are going to need a calendar table in your model that you connect to both the Opportunities and Goals tables.  Then your x axis will come from the calendar table which will correctly filter the lower tables.

[Anonymous]

Thankyou, I've just created a calendar table that starts today()-365 and finishes today()+365. Then I linked the opportunity table with the date field I was using for the x axis and with the start date field in the goals table would that be right?

At the moment its not showing anything in the chart..

[jdbuchanan71]

It sounds correct, yes.  Can you try it with a measure for the goal rather than pulling the field out of the table?

Monthly target measure = SUM ( 'Goals'[Monthly target] )

Perhaps you can share your .pbix here (load it to drop box or one drive and share the link)?