Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
orischmann9291
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

@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 PBI Community Champion




View solution in original post

Anonymous
Not applicable

@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 

 

View solution in original post

7 REPLIES 7
v-juanli-msft
Community Support
Community Support

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

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)

 

@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 PBI Community Champion




Anonymous
Not applicable

@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 

 

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

Anonymous
Not applicable

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

PattemManohar
Community Champion
Community Champion

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

 

OutputOutput





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

Proud to be a PBI Community Champion




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.