Answered

Graph/Chart with values from choice field and lookup

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have multiple lists that are related. I would like to produce a pie chart or column chart to display the number of programs by status and by program type. Status is a choice field. Program type is a number that is the ID column in a Services list. Here is a small portion of programs.

and this is Services

I saw an example of getting the choice field in and that seems ok, but the lookup gives me errors.

this is what I am using to get the full status text in my 'Items':

AddColumns(
Programs,
"Full Status",
Status.Value
)

In the result I can see the full status text value correctly.

What I'd like to do is show the number of programs in each status and by type.

I tried adding a column to the AddColumns using a Lookup, but it gives me errors and I tried a GroupBy to group by the status thinking i could do the lookup in the Series Legend.

I think I can plot this either as a pie chart or column chart, but could be wrong...not sure if the Items statement would be the same...

Can someone help?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous ,

Let's say ServiceID is a number column.

I made a sample for you.

AddColumns(GroupBy(AddColumns(LIST12,"temp1",Status.Value),"temp1","temp2"),"num",CountRows(temp2))

AddColumns(GroupBy(AddColumns(LIST12,"temp1",LookUp(LIST13,ID=ServiceID,Title)),"temp1","temp2"),"num",CountRows(temp2))

AddColumns( GroupBy( AddColumns(LIST12,"temp1",LookUp(LIST13,ID=ServiceID,Title)&"/"&Status.Value),"temp1","temp2"),"num",CountRows(temp2))

Best Regards,

Wearsky

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi Wearsky,
Thank you. I will try them. I had tried something similar, but it seemed liked the lookup was confusing what to use for the actual comparison, but looking at your examples, I think I might have been using the wrong parameters...I'll try yours. Thanks again!

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi Wearsky,
Those did work, thanks, but not really producing what I was thinking. Now that I have played with it more, maybe a pie chart isn't the right option - or I'm not seeing how to specify it right. If I use a column chart as an example, what I want to present is a breakdown by the status of how many are in each type. This is a little like your last statement is doing...

What I think I need is the items to be like:
Status Type1 Type 2 Type 3 Total
Planning 2 1 0 3
Active 3 2 3 8

The 'Type' columns would be retrieved with a lookup for each type in the type list (I only show 3 but could end up with more later) and then counts by status would be retrieved. I included a total column, but it's probably not used.

I did this in Excel, but it would look similar to:
I was originally thinking a pie chart could show it too but I'm thinking now, it can't show all the breakdowns/sub counts. I thought I saw an example but now can't find it.

What I eventually want is for the user to click a bar and then be taken to a list filtered by the column (so type and status). I know how to do that, just need to show it right first.

This is probably just a matter of changing around the addColumns and groupBy, right?

Was this reply helpful? Yes No
Verified answer

v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

I made a sample for you.

SortByColumns(AddColumns(AddColumns(GroupBy(AddColumns(LIST12,"type",LookUp(LIST13,ID=ServiceID,Title)),"type","temp1"),"num1",CountRows(Filter(temp1,Status.Value="In Planning"))),"num2",CountRows(Filter(temp1,Status.Value="Active"))),"type")

Best Regards,

Wearsky

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

thanks again. I had tried a little after my reply, and before you responded, and did somewhat the same but swapped the axis. Since I may end up having many statuses and right now only 3 services, I grouped by status and had the columns be the types.

SortByColumns(AddColumns(
GroupBy(
AddColumns(
Programs,
"temp1",
LookUp(
Services,
ID = ServiceID,
Title
),
"tmpstatus",
Status.Value
),
"tmpstatus",
"temp2"
),
"Type 1",
CountRows(Filter(temp2,ServiceID=1)),
"Type 2",
CountRows(Filter(temp2,ServiceID=2)),
"Type 3",
CountRows(Filter(temp2,ServiceID=3))
),"tmpstatus")
now to figure out how to get the 'Series' if a user clicks on a particular bar...but I think I have a plan.

Thank you again!

Was this reply helpful? Yes No