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
nsmither
Frequent Visitor

% of grand total of only top customer

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?

 

nsmither_1-1632160133114.png

 

 

 

2 ACCEPTED SOLUTIONS
Caz_16
Helper II
Helper II

@nsmither 

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

View solution in original post

v-chenwuz-msft
Community Support
Community Support

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

vchenwuzmsft_0-1632359858362.png

 

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

vchenwuzmsft_1-1632359858366.png

 

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.

View solution in original post

6 REPLIES 6
v-chenwuz-msft
Community Support
Community Support

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

vchenwuzmsft_0-1632359858362.png

 

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

vchenwuzmsft_1-1632359858366.png

 

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.

Caz_16
Helper II
Helper II

@nsmither 

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!

 

MFelix
Super User
Super User

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.