Here we need the function of distinct count. As you start typing, you will get the suggestions automatically.Now add a description of the desired name and start typing the formula in the formula section.
Now go to the PowerPivot window and click on Measure to get the option of New measure. This will give a simple Pivot Table with the total number of service providers. Now, place the Location on the Rows and the Service providers on the values as we did earlier.Since we have the data in sheet 1, we will expand the columns by clicking on the small triangle next to it.From here we will create a Pivot Table by Home → Pivot Table.Here we go: that’s a success with all the 28 rows imported.In the end, the file is imported to the data model and click on finish.Also, if you want the top column to be the header row, tick mark the option “Use the first row as column header” and hit Next.Click on Browse to choose a path to your data file. Here you can rename the connection from the default name “Excel”.Scroll to the end to get the option of Excel File and click Next. Now you will again get a dialogue box opened.So follow the steps and screenshots and click on “From Other Sources”.
Here you will find multiple options and sources available to upload the data. Here you will get a window opened, which surely will be blank in case this is the first time you are importing the data. After that go to the Data model and click on the Manage button. As said earlier, first of all, make sure that the Power Pivot tab is enabled. If you cannot find the tab, check out this tutorial. Make sure that you got the Power Pivot tab in your ribbon. Here comes the most powerful method to identify the unique entries Power Pivot. Boom!! The Pivot Table is ready with unique entries in each Pivot Table. Here you need to add Location to the ROWS and Count No to the values. Now create a Pivot Table with your data. Therefore, for all those repeated names you will get 0 in the column Count no. Now, if the name in the given column exits more than 1 time, the formula will return you 0 else you will get 1. IF function is used to add a condition: IF (( the number of times B3 Exists in a given range) is greater than 1, then give 0 else return 1) Now when you drag the formula downwards to D3, this formula becomes IF(COUNTIF($B$2:B3,B3)>1,0,1)Ĭountif ( $B$2:B3, B3) will give you the number of times B3 exists between the range $B$2:B3. This means it will not change even if you drag your formula downwards. Add this formula (=IF (COUNTIF ($B$2: B2, B2)>1,0,1)) to the cell D2 and drag it till the end.įirst of all, we have fixed the start point of the range, also called Absolute i.e $B$2. Let’s start by adding a column to your data with a header of your choice. Using the Function COUNTIFĪnother approach to calculating the Unique entries is simply using the formula COUNTIF in your Datasheet. Therefore, we have only 18 unique service providers in the country. Here we go: you have a Distinct/Unique Count for each region in the pivot table. 
Now scroll down to the end to get “Distinct Count” and click OK. After this click on the “Value Field Settings”. Here’s the trick: click on the small arrow next to “Count of Service provider” in the Pivot Table fields. This will give you the same pivot table you had earlier, but the pivot table fields look a little different. After this, you will get a usual pivot table and arrange your data in the pivot table fields as you did earlier. 
Now tick mark the box at the bottom of the dialogue box, “Add this data to the Data Model” and hit OK. Here click on the pivot table and a dialogue box appears. To start with, click on any cell in the data and go to the “Inset” tab in your ribbon. If you are using Excel for Microsoft 365, Excel 2019, Excel 2016, and Excel 2013, you have the access to Data Model. The data model is yet another thing I love about the newer versions of Microsoft Excel.
0 kommentar(er)
