cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
orischmann9291 Frequent Visitor
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]

 

Test.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Percent of total

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



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





DanBusIntel Member
Member

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.

 

Daxhelp.JPG 

 

7 REPLIES 7
Super User
Super User

Re: Percent of total

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

 

image.pngOutput



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Community Support Team
Community Support Team

Re: Percent of total

Hi @orischmann9291

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]

4.png

 

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

5.png

 

Best Regards

Maggie

orischmann9291 Frequent Visitor
Frequent Visitor

Re: Percent of total

Hi @v-juanli-msft

 

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

Re: Percent of total

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



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





DanBusIntel Member
Member

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.

 

Daxhelp.JPG 

 

orischmann9291 Frequent Visitor
Frequent Visitor

Re: Percent of total

@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
Member

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