Forum Discussion

i_kafali's avatar
i_kafali
Icon for Helper II rankHelper II
8 years ago
Solved

Calculating field that is above or below average

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,

  • i_kafali's avatar
    i_kafali
    8 years ago

    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

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    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's avatar
      i_kafali
      Icon for Helper II rankHelper II

      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