How to show column charts based on data from the table that is being fed

[Anonymous]

Hi all,

In my application, users store a long list of data among some of which are price, "Proposal Status (Template, Active, Closed-Won, Closed-Lost), Due-Date etc.

Each user sees only the data that he/she has provided by using the User().Email function.

I am trying to find a way to showcase a variety of graphs:

1. A graph that shows 3 things

    1) Projects With "Active" as proposal Status AND 1 Day Due Date

    2) Projects With "Active" as proposal Status AND Exceeded Due Date

    3) Projects With "Active" as proposal Status AND OTHERS

2. Numerical and dollars term value of

   1) Projects with "Closed - Won"

   2) Projects with "Closed - Lost"

Among others.

I would like to ask you experts whether this is even possible in PowerApps?

Initially I wanted to do this through Power BI, however, due to the many users and the costs connected with a premium version it is unfortunately not possible.

[wyotim · Accepted solution]

Hi @Anonymous! What you are proposing is definitely possible, though the charts in Power Apps can be a bit basic. If you are used to something like Power BI or Excel, you may find that the customization and design are pretty limited. That said, there are creative things you can do using other types of controls. @WonderLaura posted a blog that details one method of using a gallery to do this. (She also covers the Power BI and built-in charts as well.) You could also use shapes outside of a gallery or SVGs to do similar things. Here is a nice SVG example.

To create a simple column chart using the built-in column chart, you would want to create a collection of the categories you want and the totals of said categories. This can be done fairly easily if you have a simple way to count and define the items. From what you describe, it sounds like this might be one way you could approach it:

/* Note: This could go in the OnVisible property of the screen these charts are on or a loading screen, etc. */

/* Set a global variable for today's date so the Today() function doesn't have to run but once */
Set(TodaysDate, Today());

/* Collect a table for the chart containing the category titles and counts of items that align with those categories */
ClearCollect(
    colActiveStatusChart,
    {ID: 1, Title: "1 Day Due Date", Amount: CountIf(DataList, ProposalStatus = "Active" && (DateDiff(Due-Date, TodaysDate, Days) = 1)},
    {ID: 2, Title: "Exceeded Due Date", Amount: CountIf(DataList, ProposalStatus = "Active" && DateDiff(Due-Date, TodaysDate, Days) >= 0)},
    {ID: 3, Title: "Others", Amount: CountIf(DataList, ProposalStatus = "Active" && DateDiff(Due-Date, TodaysDate, Days) < 1)}
)
    

In the column chart, you would set the Items property to colActiveStatusChart and then set the Labels value to Title and the Series1 value to Amount. This specific approach will probably need to be modified but, hopefully, it gives a reasonable view of how to start.

Some notes: I always add an ID field as a way to give a custom sorting method. In the Items section of the column chart control, you can put

/* Sort the chart Items by ID */
Sort(
    colActiveStatusChart,
    ID,
    Ascending
)

(or something similar), which along with changing the IDs as needed will make the categories appear as you want them to. Also, having short titles is best as the only control you have over the axis titles is font size and the angle they sit at. 

As far as the numerical and dollars term values mentioned, you can use a text label and the CountIf or Sum functions along with some filtering to produce what you need. Maybe something like:

/* Count of projects with Closed - Won proposal status */
CountIf(
    DataList, 
    ProposalStatus = "Closed - Won"
)

/* Sum of dollar term values for projects with Closed - Won proposal status */
Sum(
    Filter(
        colData, 
        Proposal = "Closed - Won"
    ), 
    DollarTermValue
)

Along with some clever formatting, you can make these items look quite nice and add some conditional formatting to them (like having the text for the counts and sums above turn red if they hit a certain amount and green if they hit another using the Color property). 

I hope that gets you going in a good direction but if I can elaborate more or help with specific details, feel free to let me know!

[Anonymous]

@wyotim 

Thank you very much for this very detailed post. I have a lot to get started with. If I could give me than a single up-vote I would. Allow me to keep this thread open as I will probably have some questions once I get into the technicalities.

[wyotim]

Definitely! Again, feel free to hit me up if I can help out further. I’m happy to follow up as needed!

[Anonymous]

@wyotim 

Hi Wyotim,

with your excellent guide, I am already able to create a few different graphs. Now I have a question. I have been trying to change the visuals of this graph. Unfortunately, the way this graph is portrayed is slightly misleading as the difference between 7 and 8 seems way larger, is there a way to change this?

[Anonymous]

@wyotim 

I figured out how to filter by date 🙂

I am, however, struggling with the Sum() function. I cannot seem to connect the data with graphs nor labels. I have tried the approach you suggested with setting up a label like this:

Sum(Filter('Borusan Main Table',('Creator Email'=VarUser.Email),(Outcome = "Won"), (('Teklifin Son Gecerlilik Tarihi' >= StartDateGraph.SelectedDate) && ('Teklifin Son Gecerlilik Tarihi' <= EndDateGraph.SelectedDate))),'Toptan Satis Fiyati')

So in the filter I want to only see the revenue where projects are filtered by: "Won", within the time period chosen between two datepickers.

But this brings me a blank label. It also seems to be delegable, is there any way to get around this using SharePoint? I guess not?

I have also tried making a collection:

ClearCollect(
    colWonAndLostSum,
    {ID: 1, Title: "Won", Amount: Sum(Filter('Borusan Main Table',('Creator Email'=VarUser.Email),(Outcome = "Won"), (('Teklifin Son Gecerlilik Tarihi' >= StartDateGraph.SelectedDate) && ('Teklifin Son Gecerlilik Tarihi' <= EndDateGraph.SelectedDate))),'Toptan Satis Fiyati')},
    {ID: 2, Title: "Lost", Amount: Sum(Filter('Borusan Main Table',('Creator Email'=VarUser.Email),(Outcome = "Won"), (('Teklifin Son Gecerlilik Tarihi' >= StartDateGraph.SelectedDate) && ('Teklifin Son Gecerlilik Tarihi' <= EndDateGraph.SelectedDate))),'Toptan Satis Fiyati')}
)

Same filtering method as above, with both the Won and Lost revenue.

[wyotim · Accepted solution]

Regarding the chart, I think you will need to set the SeriesAxisMin property to 0. The charts default to a blank/auto setting, which scales it on its own.

On the date filtering and summations, great job figuring out the date filter! Regarding the blank labels, I would suspect that the issue could be that the date pickers are blank or that there is a delegation issue. I primarily work with SQL, so I'm not super strong on the specifics of SharePoint delegation but after a quick look at the documentation it appears that dates aren't delegable, so a collection would be the best way to circumvent that. Another possible method is to add a column where the date is in a number format like "yyyymmdd". So today would be 20200213. You can then treat the dates like numbers, which is much easier on the sorting side. Of course, you then have to write both the date and the numerical version of the date each time a date is changed/written. I use this method fairly often because dates aren't delegable in SQL either. If you would like more info on that, let me know.

If you use a collection, you would probably want to only filter by the user and Outcome so that the maximum number of items are pulled in. If the number of filtered items is 500 or less per user, this shouldn't cause any issues. If you anticipate it being higher, you can go into the Advanced settings (File -> Settings -> Advanced settings) and set the "Data row limit for non-delegable queries" to a higher number. It can go as high as 2000. You also may want to try sorting so that the most recent items are first (in case older data isn't necessary or optional). Here are some examples with and without the sorting:

/* With sorting */ 
ClearCollect(
    colBorusanMainTable,
    Filter(
        Sort(
            'Borusan Main Table',
            'Teklifin Son Gecerlilik Tarihi',
            Descending
        ),
        'Creator Email'=VarUser.Email,
        Outcome = "Won"
    )
)

/* Without sorting */
ClearCollect(
    colBorusanMainTable,
    Filter(
        'Borusan Main Table',
        'Creator Email'=VarUser.Email,
        Outcome = "Won"
    )
)

One note on this: the order of filtering and sorting does matter. Inner functions run before outer functions, so in the case of the one with the sort, it will sort the list first and then filter it. If you filter and then sort, there may be newer items missing if they are outside of the 500 item limit (or whatever limit you set). Just something to keep in mind. Also, in general, I like to use collections as they give a pretty good performance boost when doing things like dynamic filters. Rather than fetching from SharePoint every time the user changes the filter, it fetches locally. Definitely test and see what works best for you though.

On to the next part! If the date pickers are blank, the filter looks for a literal blank date so a little trickery is in order. The best way I have found is to check if the date picker has a value and, if it does, filter by the selected date. If it doesn't, return a true value to bypass that part of the filter. Here is what that might look like:

/* Using the collection above */
Sum(
    Filter(
        colBorusanMainTable,
        'Teklifin Son Gecerlilik Tarihi' >= StartDateGraph.SelectedDate || StartDateGraph.SelectedDate = Blank(),
        'Teklifin Son Gecerlilik Tarihi' <= EndDateGraph.SelectedDate || EndDateGraph.SelectedDate = Blank()
    ),
    'Toptan Satis Fiyati'
)

And one thought on the collection you showed: while the date picker issue might be a factor in it being blank, another issue you will have to contend with is that the collection will need to be re-collected when the user changes the date range. Otherwise, the data will remain the same. This is an issue with collections in general, so if your data has a high change rate you may need to directly pull the data or have a way for the user to update the data. If the data doesn't change much, collecting once and filtering the collection would work.

Let me know if these suggestions help or not! 

*Edit: I removed the non-collection version of the code due to dates not being delegable. It wouldn't work!*
*Another edit for forgotten words. I think I got them all this time! 😁*

[Anonymous]

@wyotim,

Thank you very much for the extremely detailed responses and help during this thread.

If I could give more than an upvote and "Accept as Solution" I certainly would. Hopefully this thread can be a help to other people standing with the same challenge as I did.

Everything is simmingly working as intended now. Hopefully it will stay that way 🙂

[wyotim]

I am happy everything seems to be working for you! If I can be of assistance in the future, please let me know!