Issues with plotting two summed values in a line chart

[kcngnkc]

Hello everyone, 

I've been struggling with an issue that you would think is pretty simple to resolve, but I can't seem to figure out. I have a line chart as shown below, with two values I'm wanting to plot. However, it's only plotting one correctly (PT_ACT) and the other (PT_TGT) is only showing the total for (blank). As you can see, there are no visual filters activated here. Any clues on what might be happening here?

[Syndicate_Admin]

How do I link the calendar table to the data you are trying to plot? It's not clear from the screenshot.

What that chart tells me is that the column used to link the EEF table to the calendar table is empty or contains values that do not exist in the column of the calendar table that is used as the link/relationship.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and so that the community knows that their problem has been solved.

If you have found this post useful, please give it to Kudos.

I work as an instructor and consultant for Microsoft 365, specializing in Power BI and Power Query.

https://sites.google.com/site/allisonkennedycv

[AllisonKennedy]

How is the calendar table linked to the data you're trying to plot? It's not clear from your screenshot. 

What that chart tells me is that the column used to link the EEF table to the calendar table is either empty or contains values that do not exist in the column in the calendar table that is being used as the link/relationship. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[kcngnkc]

Hi Allison, you have brought up a similar suggestion by JD. My "time" column in EFF table (which is comprised of fiscal weeks, fiscal period, etc) is linked to my calendar table's Fiscal Week column. The time column in the EFF table is also used for the PT_ACT, yet it's rcognizing it, so why is it not doing the same for PT_TGT?

[AllisonKennedy · Accepted solution]

@kcngnkc The data within your Time and Fiscal Week columns need to match. You may notice that both measures (PT_ACT and PT_TGT) are showing high values for 'blank'

Is this what your Calendar table looks like in the actual file? It needs a bit of cleansing if so. Your Fiscal Week column should ONLY have Fiscal Week, but it also has 'FY19', "Year" and other values that don't fit the pattern.

Your Time column in the EEF table has the same problem. You need to cleanse the data first so that the Time and Period are consistent, matching and only contain 1 piece of information (ie Week 1, Week 2, Week 3, ...). The Fiscal Year should go in another column. Your Calendar table should have every date for all years, and you should use a date key of sorts to link the EEF table to the Calendar table. My recommendation would be having a 'Start of Week' column in the EEF table that is a datekey such as '20200517' (YYYYMMdd) and link that to the DateKey column in the Calendar table once you have gotten your calendar table cleansed and sorted. 

Try this link for some help with a robust calendar table: 

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[jdbuchanan71]

However the link is between the EEF and Calendar tables, for the rows that have PT_TGT in the EFF table, in the column in EFF that links to Calendar the model is not finding a match on the Calendar side so it is showing the (Blank) Fiscal Period.

What are the columns that link those 2 tables?

[kcngnkc]

Hi JD,

Thanks for replying. The "Time" column in my EFF table is linked to the "Fiscal Week" column in my Calendar table. If the model is able to find a match on the Calendar side for the PT_ACT, why is it not recognizing a match on the Calendar side for PT_TGT? 

[jdbuchanan71]

@kcngnkc 

Can you share a picture of your clendar table also?  I need to see the values in the fisacal week column.  Better yet, if you could share your .pbix file by uploading it to OneDrive or DropBox and sharing the link here but not sure of the sensitivity of the data.

[kcngnkc]

Hi @jdbuchanan71 

thanks for the speedy response. You'll find a link to the shared file below:

https://1drv.ms/u/s!AnVF31WwnSpYa2uDjcS6SJWHbig?e=WJ9qyR

[jdbuchanan71]

You can see in the screen shot where the rows that hold PT_TGT don't line up with your calendar table.  There are no P1, P10, P11 values in the Fiscal Week column of the calendar table.

[jdbuchanan71]

You could also do the following to see where the problem is coming from.

Create 2 measures.

Calendar Count = COUNTROWS(Calendar)

EFF Count = COUTROWS(EFF)

Then it a table visual bring in the [Fiscal Week] column from Calendar and the [Time] column from EFF and add the two measures to the visual.

You will see where you have rows in 1 table that don't have a match in the other because the value in the other column will show (Blank).