Forum Discussion

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

SUM formula without filter interaction

Hi,

 

I have searched a lot of questions and answers here and could not solve my own problem

 

I have a table called OK_INVOICE_EXCEL

 

OK_INVOICE_ALL_EXCEL[FP1] is my value in USD and MUS_AD is customer names

 

What i am trying to do is to compute % of USD value (FP1) of a customer amongst all (FP1) USD Value in 2018

 

Therefore at my denominator i have tried to calculate all (FP1) USD Value regardless of customer filtration

 

But it does not work, when i select customer denominator calculates FP1 value of customer

 

 

% in FP1 = SUM(OK_INVOICE_ALL_EXCEL[FP1]) / CALCULATE(SUM(OK_INVOICE_ALL_EXCEL[FP1]);FILTER(OK_INVOICE_ALL_EXCEL;OK_INVOICE_ALL_EXCEL[Only Year]=2018);all(OK_INVOICE_ALL_EXCEL))

 

I have also tried this formula as denominator; 

 

CALCULATE(SUM(OK_INVOICE_ALL_EXCEL[FP1]);FILTER(OK_INVOICE_ALL_EXCEL;OK_INVOICE_ALL_EXCEL[Only Year]=2018);ALLEXCEPT(OK_INVOICE_ALL_EXCEL;OK_INVOICE_ALL_EXCEL[MUS_AD]))

 

Also tried this;

 

CALCULATE(SUM(OK_INVOICE_ALL_EXCEL[FP1]);ALLEXCEPT(OK_INVOICE_ALL_EXCEL;OK_INVOICE_ALL_EXCEL[MUS_AD]))

 

All didnt work

 

Thanks in advance

Need Help
Show and Tell
Tips and Tricks
  • i_kafali's avatar
    i_kafali
    8 years ago

    Thank you Greg

     

    I solved the problem 

     

    CALCULATE(SUM(OK_INVOICE_ALL_EXCEL[FP1]);ALLEXCEPT(OK_INVOICE_ALL_EXCEL;OK_INVOICE_ALL_EXCEL[Currency Selection];OK_INVOICE_ALL_EXCEL[Only Year];OK_INVOICE_ALL_EXCEL[MONTH];OK_INVOICE_ALL_EXCEL[FIRMA];OK_INVOICE_ALL_EXCEL[SAT_AD];OK_INVOICE_ALL_EXCEL[BU];OK_INVOICE_ALL_EXCEL[Subsegment];OK_INVOICE_ALL_EXCEL[Fiili/Bütçe]))

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    I believe what you want is:

     

    % in FP1 = SUM(OK_INVOICE_ALL_EXCEL[FP1]) / CALCULATE(SUM(OK_INVOICE_ALL_EXCEL[FP1]);FILTER(ALL(OK_INVOICE_ALL_EXCEL);OK_INVOICE_ALL_EXCEL[Only Year]=2018)))

    Now, a word about the difference here. With the ALL in the filter section of the filter clause, you are filtering the current context and getting ALL of them, but the table is already filtered, so you are getting ALL of the filtered table, which is not what you want. So, you need to use the ALL in the table expression portion of the filter clause so that you get everything and then filter down the way you want from there. Hope that makes sense.

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

      Thank you Greg

       

      I solved the problem 

       

      CALCULATE(SUM(OK_INVOICE_ALL_EXCEL[FP1]);ALLEXCEPT(OK_INVOICE_ALL_EXCEL;OK_INVOICE_ALL_EXCEL[Currency Selection];OK_INVOICE_ALL_EXCEL[Only Year];OK_INVOICE_ALL_EXCEL[MONTH];OK_INVOICE_ALL_EXCEL[FIRMA];OK_INVOICE_ALL_EXCEL[SAT_AD];OK_INVOICE_ALL_EXCEL[BU];OK_INVOICE_ALL_EXCEL[Subsegment];OK_INVOICE_ALL_EXCEL[Fiili/Bütçe]))