How do I get the proper graphical display for a SharePoint List?

[lmheimendinger]

I have a SharePoint list that has records as follows;

1. For each location, there are multiple records as defined by 2. below.
2. For each month of the year, there is a single record per location.

In each record (location and month) there is a target goal.

In another SharePoint list, there is a single record for

1. Each day of the year
2. Each location.

The sum of the values in the daily records, by location, is stored in the location record by month.  So location A for month 6 has the sum of the records for location A for all the days in month 6.  Addotionally, the monthly records have a goal value for that location. 

I have successfully gotten the daily records to display for a given month as the sum of the daily values on the vertical axis and the location names on the bottom axis.  What I have been unable to sort out is how to display, either as side by side bar or a line chart over the bars what the monthly goals are that correspond to that location.

If instead of a single chart I create two and drag the sales goals and location names to the second one, I get a display that is just like the daily one which is what I want.  But I need to combine them on one chart for comparison.

Here's what's puzzling.  I can drag the month onto the monthly chart and then filter it by month number, and the monthly goals figures appear.  If I do the same thing on the combined chart, month filtering shows blank and no goals appear.  It's as though there were no records.

I thoght it might be in the query types but since it works standalone i have assumed that is not the case.  Could it be the filtering on the combined graph?  but why whould selecting something to include/exlude on that list affect the other?

[v-frfei-msft]

Hi @lmheimendinger ,

Seemed in your scenario, ISFILTERED should work. Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[lmheimendinger · Accepted solution]

Actually, I just solved this an hour ago.  And the solution is not as I would like, but it makes complete sense.

In my two lists, I actually had fields so that if I used one list or the other, but not both, then it all worked as I wanted.  In particular, the MTD totals had a field for total MTD values in addition to the monthly goal so I used only one data source.  I used stacked vertical bars and got just what i wanted.

Why didn't it work with two independent lists? I now realize that I would have had to establish a relationship between them for Power BI to "know" how one corresponded to the other and that having a column in each with same values (location) was simply not enough.  Guess I was just lucky when I set up the lists.

Oh, let me add one thing about getting this to work.  Actually two things.

1.   I had to edit the query and change the colunn type to fixed decimal on both goal and MTD.  That changed the column header to display $.
2. In the Power BI values, I had to change from Count or Sum to Maximum.  For any given location, the record columns would contain the desired values, this worked just fine.  When I was using daily records, the MTD valuses would have to be Sum.