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.
Hi,
I am trying to create a measure that will give me the percentage that a salesperson's top customer makes up of their grand total margin. I created a measure to give me the percentage of the grand total each customer makes up however can not figure out how to use topn or a simular function to give me only the percentage of the top customer. Our idea is to award points for a customer being no more than 40% of their diversity and then start to subtract points for every percentage point over 40%. below is an example of the table showing their customers and the % each makes up however I need the formula to be able to grab only the 54.83% for the point system. does anyone know how I would do this?
Solved! Go to Solution.
Try creating a variable in a measure and then return the variable. That number will remain costant no matter where you use it in your report.
Top Customer % of Total =
VAR TopCustomer =
MAX([Your Sales Coumn])
VAR GrandTotal =
SUM([Your Sales Column])
VAR %ofGrandTotal =
DIVIDE(TopCustomer, GrandTotal)
RETURN
%ofGrandTotal
It looks like your Margin column is a measure. MAX only works on a Column, so you may need to make a summary table to convert your measure to a column, then pass that column in the MAX function.
Top Customer % of Total =
VAR Summary Table =
SUMMARIZE(SalesTable,
CustomerColumn,
"Sum of Sales",
[Margin]
)
VAR TopCustomer =
MAX(Summary Table[Sum of Sales])
VAR GrandTotal =
SUM(Summary Table[Sum of Sales])
VAR %ofGrandTotal =
DIVIDE(TopCustomer,GrandTotal)
RETURN
%ofGrandTotal
Hopefully this puts you on the right track. I did the best I could with the limited data sample provided.
Best, -Caz
Hi @nsmither ,
Do you mean what percentage of the total margin of top n is the total margin of the salesperson, if so please see my next steps.
I have created some data
Create a measure with this:
% of grand total =
VAR _topn_margin =
CALCULATE (
SUM ( 'Table'[Margin] ),
TOPN (
2,
FILTER ( ALL ( 'Table' ), [Salesperson] = MAX ( 'Table'[Salesperson] ) ),
[Margin], DESC
)
)
VAR _grand_margin =
CALCULATE (
SUM ( 'Table'[Margin] ),
FILTER ( ALL ( 'Table' ), [Salesperson] = MAX ( 'Table'[Salesperson] ) )
)
RETURN
DIVIDE ( _topn_margin, _grand_margin )
Are my data correct and if not, please provide your desensitised data and your expected results.
Result
Here is my pbix file
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @nsmither ,
Do you mean what percentage of the total margin of top n is the total margin of the salesperson, if so please see my next steps.
I have created some data
Create a measure with this:
% of grand total =
VAR _topn_margin =
CALCULATE (
SUM ( 'Table'[Margin] ),
TOPN (
2,
FILTER ( ALL ( 'Table' ), [Salesperson] = MAX ( 'Table'[Salesperson] ) ),
[Margin], DESC
)
)
VAR _grand_margin =
CALCULATE (
SUM ( 'Table'[Margin] ),
FILTER ( ALL ( 'Table' ), [Salesperson] = MAX ( 'Table'[Salesperson] ) )
)
RETURN
DIVIDE ( _topn_margin, _grand_margin )
Are my data correct and if not, please provide your desensitised data and your expected results.
Result
Here is my pbix file
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try creating a variable in a measure and then return the variable. That number will remain costant no matter where you use it in your report.
Top Customer % of Total =
VAR TopCustomer =
MAX([Your Sales Coumn])
VAR GrandTotal =
SUM([Your Sales Column])
VAR %ofGrandTotal =
DIVIDE(TopCustomer, GrandTotal)
RETURN
%ofGrandTotal
It looks like your Margin column is a measure. MAX only works on a Column, so you may need to make a summary table to convert your measure to a column, then pass that column in the MAX function.
Top Customer % of Total =
VAR Summary Table =
SUMMARIZE(SalesTable,
CustomerColumn,
"Sum of Sales",
[Margin]
)
VAR TopCustomer =
MAX(Summary Table[Sum of Sales])
VAR GrandTotal =
SUM(Summary Table[Sum of Sales])
VAR %ofGrandTotal =
DIVIDE(TopCustomer,GrandTotal)
RETURN
%ofGrandTotal
Hopefully this puts you on the right track. I did the best I could with the limited data sample provided.
Best, -Caz
Thanks, Caz_16 I appreciate your help!
Hi @nsmither ,
Can you please exlpain a little better what you want to achieve? Do you want to only return the percentage for the top 5 customers for example?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
I am wanting to return just the percentage for the number one customer. the way we score a salesperson's diversity currently is if their top customer is no more than 40% of their grand total they would be awarded 15 points towards their overall performance score. and if their top customer is above 40% of their total margin we start to subtract .25 points for every 1% over 40%. I am trying to find a solution so I could take their top customer's percentage of their total margin and plug it into a formula to award the points so that I do not have to manually go in and enter what percentage their top customer makes up for each salesperson every day.
I hope this helps with understanding what I am trying to accomplish.
Sorry for the addtiional question
But do you want to return a table with only the 1st customer on each sales person? do you also want the calculation of the points to be automatically consider?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |