Power BI report using People Picker Field

In this Power BI Tutorial, We will discuss how to display the monthly and yearly sales report depending upon the Sales Person without using any filter in Power BI Measure.

Also, We will see how to display the monthly and yearly sales report using a Slicer filter in PowerBI Measure.

Another most important thing I will show you how we can use the Person/People Picker fields in Power BI Report.

All these above things I will explain to you by taking a simple example So that you can easily understand the concept.

Sales Report using Power BI Measure

In this example, I have a Sales Report list in my SharePoint Site. This list has many different data types of columns. Those columns are:

• Sales Person ID: [By default, it is a Title column]
• Sales Person: [Person data type]
• Customer: [Single Line of Text data type]
• Current Month Sales ($): [Number data type]
• Projected Month Sales ($): [Number data type]
• Projected Month Sales vs Current Month Sales ($): [Calculated Column:(Projected Month Sales – Current Month Sales)]
• Current Year Sales ($) : [Number data type]
• Projected Year Sales ($): [Number data type]
• Projected Year Sales vs Current Year Sales ($): [Calculated Column:(Projected Year Sales – Current Year Sales)]

You can see my Sales Report List in the below screenshot:

Here in Power BI Measure, I want to display the Current month sales and Projected Month Sales report depends upon their title without using any filter.

For that, You need to follow some below processes as:

First of all, Open your Power BI Desktop and Sign in with your Microsoft account. Get the SharePoint List from SharePoint Online Site to your Power BI Desktop.

If you want to know how to get the SharePoint List from SharePoint Online Site to Power BI Desktop, then you can refer this link: Get SharePoint List to Power BI Desktop

You can see my Sales Report table in Power BI Desktop as shown below:

In this above screenshot, Under the Fields section, you can see my table (Sales Report) with a down arrow symbol. Once you will expand that symbol, then you can see all the columns that are available in the existing table.

Also, I have formatted the data type as the Whole Number of these below columns:

• Current Month Sales ($)
• Current Year Sales ($)
• Projected Month Sales ($)
• Projected Month Sales vs Current Month Sales ($)
• Projected Year Sales ($)
• Projected Year Sales vs Current Year Sales ($)

If you want to know the more details about the various Power BI Data types, then click on this link: Power BI Data type

After formatting all those columns of the table, those columns will appear like the above screenshot.

Now, You have to apply the rule to view the Projected monthly sales and Projected Yearly Sales of a particular person/user depends upon the Title column.

Here, In this example, I want to show the reports (Projected Monthly Sales and Projected Yearly Sales) of Bijay. So for that, I have created two new Measures and written the below measure formulas:

Projected Monthly Sales(Bijay) = CALCULATE(SUM(SalesReport[CurrentMonthSales($_]),SalesReport[Title]="101")

Projected Yearly Sales(Bijay) = CALCULATE(SUM(SalesReport[ProjectedYearSales($]),SalesReport[Title]="101")

Once it is done, then take any visual from the Visualizations section and put those above measures formulas into the Values section of the visual.

For this example, I have taken the Clustered bar chart and put those measure formulas into the Values section. Here also, I have used the Customer column in the Axis field.

After all, you can see the chart will appear as like the below screenshot:

So this was the example to show a specific report without using any filter in Power BI Measure. Now in the next point, I will show you how you can show a report by using the Person field in Power BI Measure with a slicer filter.

Read Power BI Pie Chart

Sales Report using Slicer Filter in Power BI Measure

As I have shown you, In my Sales Report list, I have a Person field data type column named as Sales Person.

Here, I want to show the report depending upon the users who are present in the SharePoint Site or those who can get access to the SharePoint Site.

Also, I will use a Slicer Filter, So that you can view the report of that specific person whose report you need.

Before creating a new Measure, I would like to tell one important thing as You can not directly use or add the Person field in any Power BI Report.

To use the Person Fields in Power BI Report, You need to follow some below steps which I have discussed below.

Power BI report using People Picker Field

Follow these below steps to use the Person Fields in Power BI Report:

Step-1: (Data Loading)

First of all, In the Power BI Desktop, Sign in with your Microsoft account. Then the next step is loading the SharePoint List into the Power BI Desktop.

Basically, to load the List in Power BI Desktop, We use Get Data and then choose SharePoint Online List (if connecting to SharePoint Online) or SharePoint List (if using SharePoint Server). Then it is asking for the SharePoint Site URL.

When we are entering the SharePoint Site URL, it will ask again for Sign in if you forgot to sign in before with Power BI Desktop otherwise the below screen will appear where you have to select your SharePoint list that you want to report on.

In this example, as I have Sales Report, So I have selected that list (SalesReport) and clicked on the Transform Data button as shown below.

Once you will click on the Transform Data button, the below Query Editor window will appear.

You can view there are a lot of columns present and also you can remove the column that you do not want.

In this example, I removed some unnecessary columns like ContentTypeId, ContentType, GetDIpPolicyTip, FieldValuesAsHtml, File, Folder etc.

The most important thing you have to remember that, As we want to report on the Person field as “Sales Person”. So here, we have to keep the SalesPerson and FieldValuesAsText column.

Step-2: (Extract the Full Name)

Here in this example, You can see there are two fields present that are related to the Sales Person, the SalesPersonid and SalesPersonStringid columns. Both columns display a number of data types.

If you want to see the full name of a user, then you can use the FieldValuesAsText Column.

In the Query Editor window, just scroll right and select the expand icon for the FieldValuesAsText column and then disable all the available fields except the SalesPerson column. Refer the below screenshot:

Now select OK and rename the column if you want. Now you can see the full name of the Person or user is retrieved.

Step-3: (Make a link with the User Information List)

Now you need to make a relationship with the User Information List. This User Information List is a hidden list that presents in the root site of every SharePoint Site Collection. When the Site collection is accessed, at that time, this list gets populated automatically.

When you load a person field column, then a column creates automatically as ColumnNameid as well it contains the ID value of the user from the list.

Now follow the Step-1 for loading the User Information List. Once this list is loaded, then remove the unnecessary columns except for the ID column from the query editor.

After all, Select the Close and Apply button from the ribbon of the Query Editor window. Now you have to make a relationship between the Sales Report and User Information List.

For that, Go to the Model tab and then drag the Sales Report[SalesPersonid] to User Information List[id] as you can see in the below screenshot.

Step-4: (Use Person field in any Visual from the User Information List)

To test the Person field, take any visual under the Visualizations section as per your requirement.

Here, I have taken a Slicer Filter from the Visualization section. In that visual, I have taken only the First Name from the User Information List as I need to view only the first name of the Sales Person and later I have renamed it.

Also, You can take the Name, Last Name, Email, etc as per your requirement. You can see my filter as shown below:

Now to test the report, I have taken a Clustered bar chart from the Visualizations. In the Value section (In Clustered bar chart), I put these below columns from the Sales Report List:

• Current Month Sales($)
• Projected Month Sales($)

In the Axis section, I have taken the Title column from the User Information List. Then the Power BI Report is appearing as like the below screenshot:

As I have used the Slicer Filter visual, So you can see the report of a particular person. Here, I want the Preeti report, So I have selected Preeti and it shows me the Sales of Preeti only as shown below.

Also, You may like following Power BI tutorials:

• [Video Tutorial] Create Power BI Dashboard step by step tutorial
• Data Labels in Power BI
• Power BI Buttons
• Power BI Query Group Tutorial
• Export Power BI Reports to Excel
• Subtraction in Power bi using DAX
• Power BI Measure Sum and Subtract Example
• Get Current Month Sales Report using Power BI Measure
• Get Next Month Sales Report in Power BI
• Power BI Error: This content isn’t available
• How to change data source in Power Bi

Here in this Power BI Tutorial, we learned, how to display Power BI report using People picker field.