cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lauratn
Helper I
Helper I

Custom line chart

Hello, I have two Sharepoint Lists: Advisers and Advisees.

 

How can I count the total Advisees each Adviser has,  the total Advisees per department, and show the percentage of advisee's Career Level per Adviser. In the Advisee's list, I have LookUp columns to other 2 lists with Department and Career Levels. The Advisees and Adviser's names are person columns.

 

Any help would be appreciated.

 

Screenshot 2022-03-22 202400.png

1 ACCEPTED SOLUTION

Accepted Solutions

The solution was:

Creating a collection using a SharePoint list:

 

ClearCollect(
    colCareerLevel,
    careerLevelList
);

 

Creating a collection using Office365.DirectReportsV2 function:

 

ClearCollect(
    colDirectReport,
    AddColumns(
        Office365Users.DirectReportsV2(AdvisersGallery.Selected.Name.Email).value,
        "CareerLevel",
        LookUp(
            colCareerLevel,
            OfficeTitle = If(
                "," in jobTitle,
                First(
                    Split(
                        jobTitle,
                        ","
                    )
                ).Result
            )
        )
    )
);

 

Getting the total Advisees for Each Adviser:

 

CountRows(Filter(
    colDirectReport,
    accountEnabled = true
))

 

Creating a Blank Vertical Gallery and inserting two rectangles. One for the background, one for the foreground.

In my LineChartGallery.Items:

 

Filter(
    ForAll(
        Distinct(
            colDirectReport,
            CareerLevel.Title
        ),
        {
            Result: Result,
            Percentage: CountRows(
                Filter(
                    colDirectReport,
                    department = cb_Departments.Selected.Result || IsBlank(cb_Departments.Selected.Result),
                    CareerLevel.Title = Result
                )
            ) / CountRows(
                Filter(
                    colDirectReport,
                    department = cb_Departments.Selected.Result || IsBlank(cb_Departments.Selected.Result)
                )
            )
        }
    ),
    Percentage <> 0
)

 

In background rectangle width: 500

In my Foreground rectangle's width property:

 

rectBackground.Width *  ThisItem.Percentage

 

 In my lable showing the percentage:

 

Text(ThisItem.Percentage*100, "#.0%")

 

The result was a line chart that I could select by department with a Combobox:

Screenshot 2022-04-08 153257.png

View solution in original post

4 REPLIES 4
TheRobRush
Super User
Super User

Not seeing your datasource layout we can only guess, but I would guess groupby would be in order

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

Name and Adviser are Person columns, Career Level and Department are LookUp columns

Screenshot 2022-03-22 221323.png

Name is a person column, Advisee is person multi-select, PID and Total Advisees are type number

Screenshot 2022-03-22 221429.png

The solution was:

Creating a collection using a SharePoint list:

 

ClearCollect(
    colCareerLevel,
    careerLevelList
);

 

Creating a collection using Office365.DirectReportsV2 function:

 

ClearCollect(
    colDirectReport,
    AddColumns(
        Office365Users.DirectReportsV2(AdvisersGallery.Selected.Name.Email).value,
        "CareerLevel",
        LookUp(
            colCareerLevel,
            OfficeTitle = If(
                "," in jobTitle,
                First(
                    Split(
                        jobTitle,
                        ","
                    )
                ).Result
            )
        )
    )
);

 

Getting the total Advisees for Each Adviser:

 

CountRows(Filter(
    colDirectReport,
    accountEnabled = true
))

 

Creating a Blank Vertical Gallery and inserting two rectangles. One for the background, one for the foreground.

In my LineChartGallery.Items:

 

Filter(
    ForAll(
        Distinct(
            colDirectReport,
            CareerLevel.Title
        ),
        {
            Result: Result,
            Percentage: CountRows(
                Filter(
                    colDirectReport,
                    department = cb_Departments.Selected.Result || IsBlank(cb_Departments.Selected.Result),
                    CareerLevel.Title = Result
                )
            ) / CountRows(
                Filter(
                    colDirectReport,
                    department = cb_Departments.Selected.Result || IsBlank(cb_Departments.Selected.Result)
                )
            )
        }
    ),
    Percentage <> 0
)

 

In background rectangle width: 500

In my Foreground rectangle's width property:

 

rectBackground.Width *  ThisItem.Percentage

 

 In my lable showing the percentage:

 

Text(ThisItem.Percentage*100, "#.0%")

 

The result was a line chart that I could select by department with a Combobox:

Screenshot 2022-04-08 153257.png

TheRobRush
Super User
Super User

Great job, sorry I didn't get back to you, somehow missed your response

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (3,774)