- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Percent of total

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

orischmann9291

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-20-2018
08:51 AM

I am trying to find percent that each item has been sold by a salesperson out of their total sales. I have seen similar questions but they all related to the sum function which won't work since my data is in text format. I have 3 items and 3 sales people. They can sell item A, B or C. When i try to do it it shows up as 100% for each item. I also tried showing it as percent rather than sum total but you are unable to sort each column which is the exact thing that needs to be done. Below please find the same data and My formula.

% of Total per Person = Count(Table A[Item]/Count(Table A[Salesperson]

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

PattemManohar

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-25-2018
08:24 AM

@orischmann9291 Could you please try my approach mentioned above, then you can transpose as you like....

Proud to be a **Datanaut !**

DanBusIntel

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-25-2018
08:53 AM

Is this what you're looking for?

mDax =

DIVIDE(

COUNT( Table1[Salesperson] ),

CALCULATE(

COUNT( Table1[Salesperson] ),

ALLSELECTED( Table1[Item] )

)

)

Using a Matrix visual you can bring Salesperson into the Rows, Item into the Columns, and mDax into the Values.

7 REPLIES 7

PattemManohar

Super User

Re: Percent of total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-21-2018
12:58 AM

Here is the solution I tried...

Added two columns with below:

**Column 1 :** CALCULATE(COUNT(Sales[Item]),FILTER(All(Sales),Sales[SalesPerson]=EARLIER([SalesPerson])))

Which gives total count of items for particular Salesperson

**Column 2 :** CALCULATE(COUNT(Sales[Item]),FILTER(ALL(Sales),Sales[Item]=EARLIER(Sales[Item]) && Sales[SalesPerson] = EARLIER(Sales[SalesPerson])))

Which gives total count of particular item by the particular Salesperson

**% Of Total per Person** = Sales[Column 2]/Sales[Column1] *100

You can directly do it in single field, but I've splitted the logic for testing...

Proud to be a **Datanaut !**

v-juanli-msft

Community Support Team

Re: Percent of total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-21-2018
01:24 AM

Create calculated columns

total per item = CALCULATE(COUNT(Table1[item]),ALLEXCEPT(Table1,Table1[item])) count of each item per person = CALCULATE(COUNT(Table1[item]),FILTER(ALLEXCEPT(Table1,Table1[salesperson]),[item]=EARLIER([item]))) per = [count of each item per person]/[total per item]

Or create a measure

Measure 2 = VAR total = CALCULATE ( COUNT ( Table1[item] ), ALLEXCEPT ( Table1, Table1[item] ) ) VAR perpersonl = CALCULATE ( COUNT ( Table1[item] ), FILTER ( ALLEXCEPT ( Table1, Table1[salesperson] ), [item] = MAX ( [item] ) ) ) RETURN perpersonl / total

Best Regards

Maggie

orischmann9291

Frequent Visitor

Re: Percent of total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-25-2018
08:19 AM

It looks like your total per item is showing the total # that item was sold. I am trying to get the total number of items sold by that salesperson because I want to find out what % of B's Joe has sold of his 6 items rather than his % of all B's sold if that makes sense. I would like my table to end up looking like below. I am able to get the percent when use a count function and then show it as percent of row total but I need to be able to sort by each items column and you are unable to do that with the percent of row aspect.

Salesperson A B C Total

Joe 16.67% (1) 33.33% (2) 50% (3) 100% (6)

Jim 40% (3) 20% (1) 20%(1) 100% (5)

Sally 33.33% (2) 33.33% (2) 33.33% (2) 100% (6)

PattemManohar

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-25-2018
08:24 AM

@orischmann9291 Could you please try my approach mentioned above, then you can transpose as you like....

Proud to be a **Datanaut !**

DanBusIntel

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-25-2018
08:53 AM

Is this what you're looking for?

mDax =

DIVIDE(

COUNT( Table1[Salesperson] ),

CALCULATE(

COUNT( Table1[Salesperson] ),

ALLSELECTED( Table1[Item] )

)

)

Using a Matrix visual you can bring Salesperson into the Rows, Item into the Columns, and mDax into the Values.

orischmann9291

Frequent Visitor

Re: Percent of total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-25-2018
11:26 AM

@DanBusIntel Is there any way to be able to sort by the columns in this so that I could sort who has the highest percentage or A, B, or C? It seems to only let me sort the total which obvioulsy all adds up to 100.

DanBusIntel

Member

Re: Percent of total

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2018
09:48 AM

@orischmann9291

The solution I gave you does the calculation in a single context hence not being able sort by a particular column. To achieve what you would may require the use of a table where you add columns based on a calculate by Item to be able to sort by Item (i.e. A, B, C). Not sure of a way to calculate by SalesPerson but still adding columns by Item (if that makes sense).

Others on here might be able to suggest a way.

If what I've provided does help then please add grant kudos and accept as Solution (if not done all ready).