Calculating field that is above or below average

[i_kafali]

Hello

I have a data set with tons of fields from customer no to destination, product no, name, seller name, product type, segment, packaging type etc. for profitability report

What i want to do is to create a report for customers only:

My attention is to show off customers or productss if they are above or below profit margin % (FP2)

For Profit margin (FP2) % i created below measure:

FP2% = CALCULATE(DIVIDE([FP2];SUM(OK_INVOICE_ALL_EXCEL[Turnover PV])))

and i also created profit margin % for all company average as below;

FP2 % all = CALCULATE(DIVIDE(OK_INVOICE_ALL_EXCEL[FP2];SUM(OK_INVOICE_ALL_EXCEL[Turnover PV]));ALLEXCEPT(OK_INVOICE_ALL_EXCEL;OK_INVOICE_ALL_EXCEL[BU];OK_INVOICE_ALL_EXCEL[Currency Selection];OK_INVOICE_ALL_EXCEL[Only Year];OK_INVOICE_ALL_EXCEL[Fiili/Bütçe];OK_INVOICE_ALL_EXCEL[MONTH]))

so mathematically a customer with 5% FP2% must be below 15% FP2% all

to distinguish and classify that i created a column as below

Threshold FP2 % = IF([FP2%]>[FP2 % all];"Above";"Below")

so this column will help me to show and filter whether customers or products above or below FP2% within company figures

When i applied all those above, i select one customer which has lower FP2% compared to all

It calculates (i guess) all the fields and returns more than one THRESHOLD FP2%

Please help me how can i solve this out. IF Customer has 6.65% FP2 margin and below 16.63% of average, how can i classify this customer as below instead of more than one.

EDIT

I guess i found the problem but dont know the formula;

Customer or Product has more than one different shipment type 

for example one with bag one with container. Bag has higher FP2% than all average. 

I need to disregard all those shipment type, country, segment, seller name etc. How can i do that and create my table only based on customer name and product name?

Thanks and regards,

[v-jiascu-msft]

Hi @i_kafali,

Maybe you can modify it like the formula of "FP2 % all". 

FP2% =
CALCULATE (
    DIVIDE ( [FP2]; SUM ( OK_INVOICE_ALL_EXCEL[Turnover PV] ) );
    ALL ( OK_INVOICE_ALL_EXCEL[shipment type] )
)

Best Regards,

Dale

[i_kafali · Accepted solution]

Hi Dale,

Thank you for replying

When i apply the formula as you mentioned it calculates all the FP2% all including all the years and months, business units etc. etc.

thus i did "all except" formula with those should not affected

[v-jiascu-msft]

Hi @i_kafali,

So you solved it?

Best Regards,

Dale