Building data for graphs

[Coopedup]

Hey All,

I thought I'd left the easy bit till the end but P.App graphs are a bit lame (but all I have to work with).

One of the problems, I think, is that I've never been very good at them in Excel so find it hard to construct or visualise the data I need!

I have two required visuals...

1) In my SP List I have 100's of rows. I want to group by OrgTower and then by the number of High, Medium and Low in each of these towers (displayed in a column chart).  

2) With the same SP List I'd like to get a running total in line graph of Open, Draft and Closed records against weeks they were created in. So I can see the increase/reductions over the last year. 

These both seem genuinely hard! Can anyone point me towards some example literature or online vids?

Cheers all!

[RandyHayes]

@Coopedup 

Not so much a pointer to anything online, but feel free to give things a try and then post issues here for help.

In general (as we discussed in other threads) you will need to Shape the data you want into a table that can be graphed.

So, in your first visual, you will need to Groupby the OrgTower and then determine the criteria for the high, med and low of those grouped records.  Not knowing your data (or examples of it) other than what I've seen from you in other threads, it's hard to give a specific.  But, it can all be done if the data is shaped properly.

I would recommend trying some of the methods discussed previously and if you get stuck, post back what you have.  Provide some example data and a chart (even just a mock up) of what you want to get to.

I hope this is helpful for you.

[Coopedup]

I honestly thought this would be the easiest bit..This is the sort of structure and report if I do it in excel. But I'm really struggling to get the addcolumns, groupby etc.

My DataSource is SP List and both these columns will be drop list options.

[RandyHayes]

@Coopedup 

Well, getting the data shaped is relatively easy to do with the functions in PowerApps, but understanding how to do it and to match it up with what other controls (like the charts) want to see is sometimes a bit confusing.

So...here's the deal.  You are showing a stacked bar chart in your picture.  I can start by saying, that is not something you can have in PowerApps with the chart control.  The Power BI tiles integration can provide this, but the choice to move that part of the application to Power BI is one to take with some thought.  If you have other activities going on with the data, then the separation of the two (app and PowerBI) can sometimes become a burden. 

So, is it a stacked chart that you want, or is there another option you have in mind?

[Coopedup]

Hi,

If not stacked can it do it like this?  I guess (and if its possible) a drop list of all, IT, Core and Network that changes the counts would work. 

[RandyHayes]

@Coopedup 

Yeah...not really possible with the built-in control to get it to look so nice.  You can specify multiple series as you have.  Unfortunately, the chart control does not have a series spacing option, so the best it can get to look is like this:

[RandyHayes]

@Coopedup 

Actually, a little tweak to the data and legend and I was able to get it closer to the look:

[Coopedup]

That is exactly what I need sadly we don't have P.Bi !!  I can get as far as having just one set of data (totals per status)!

[RandyHayes · Accepted solution]

@Coopedup 

So the last screenshot was the built in PowerApp chart control.  So, if that meets the look of what you need, then yes...it's a go.

Now, let's get your data in there.

Here is the hint...this is what I used for the Items property of the chart:

Table(
    {Label:"Total", IT:7, Core:8, Network:7, Space:0},
    {Label:"High", IT:2, Core:1, Network:0, Space:0},
    {Label:"Medium", IT:1, Core:4, Network:3, Space:0},    
    {Label:"Low", IT:4, Core:3, Network:3, Space:0}
)

So all we need to do is get your data into that format.

I will just assume based on the picture of your Excel table the names.

I would see a formula like this in the Items property of your Chart would give you what you want:

With({_data: yourDataSourceHere},
    With({_stats:Distinct(_data, Status)},
        ForAll(Sequence(CountRows(_stats)+1, 0),
            With({_statsVal:Coalesce(Last(FirstN(_stats, Value)).Result, "")},
                {Label: If(Value=0, "Total", _statsVal), 
                 IT: CountRows(Filter(_data, ((Value=0) || Status=_statsVal) && Tower="IT")),
                 Core: CountRows(Filter(_data, ((Value=0) || Status=_statsVal) && Tower="Core")),
                 Network: CountRows(Filter(_data, ((Value=0) || Status=_statsVal) && Tower="Network")),
                 Space: 0
                }
            )
        )
    )
)

Set your Chart series to 4.  Set Labels to Label, Series1 to IT, Series 2 to Core, Series 3 to Network and Series 4 to Space.

I used the following colorset : [Blue,Orange, Gray]

Final bit was to set the Items property on the Legend Items property to the following:

Filter(yourChartNameHere.SeriesLabels As lbls, !(lbls.Value="Space"))

Give that a go and see if that gets you where you want to be.

[Coopedup]

This is a great amount of help. I will try in the morning and hope to crack it before the holidays.

Thanks!