cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lmheimendinger Frequent Visitor
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
lmheimendinger Frequent Visitor
Frequent Visitor

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

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.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

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

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.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
lmheimendinger Frequent Visitor
Frequent Visitor

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

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.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors