Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@orischmann9291 Could you please try my approach mentioned above, then you can transpose as you like....
Proud to be a PBI Community Champion
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.
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
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....
Proud to be a PBI Community Champion
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.
@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.
@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).
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 PBI Community Champion
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |