cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Percent of total

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]

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User I

## Re: Percent of total

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

Proud to be a Super User!

Highlighted
Anonymous
Not applicable

## Re: Percent of total

@orischmann9291

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
Highlighted
Super User I

## Re: Percent of total

Here is the solution I tried...

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...

Output

Proud to be a Super User!

Highlighted
Community Support

## Re: Percent of total

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

Highlighted
Frequent Visitor

## Re: Percent of total

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)

Super User I

## Re: Percent of total

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

Proud to be a Super User!

Highlighted
Anonymous
Not applicable

## Re: Percent of total

@orischmann9291

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.

Highlighted
Frequent Visitor

## Re: Percent of total

@Anonymous  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.

Highlighted
Anonymous
Not applicable

## Re: Percent of total

@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).

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021