Forum Discussion
SUM formula without filter interaction
- 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]))
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.
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]))