Forum Discussion
SUM based on specific condition
I tested this solution and it worked for my sample data set
Pieces Below Company Target :=Var BelowTarget =CALCULATE(CALCULATE(sum(DataTest[Pieces]), FILTER(DataTest, [% Good Pieces] < [Target])),all(DataTest))RETURNIF( [% Good Pieces] < [Target], BelowTarget)Pieces Above Company Target :=Var AboveTarget =CALCULATE(CALCULATE(sum(DataTest[Pieces]), FILTER(DataTest, [% Good Pieces] > [Target])),all(DataTest))RETURNIF( [% Good Pieces] > [Target], AboveTarget)
I tested this solution and it worked for my sample data set
Hi thedatahiker , thank you for the answer.
i tryed your measure but if i'm not wrong you did your test on a database like the one in the green circle because this is what i get:
so basically it worked but it does the calulation based on the target of the company for each day and i would like that it calculates based on the aggregation.
is there a way to fix this?
Thank you for the support
Hi ilTac,
If I understand your question I think you just need is to alter the % good pieces measure in the IF statement to calculate the total for the entire company irrespective of date. You could also accomplish a similar result by using ALL(Table[Day]) instead of ALLEXCEPT but in this case, I think it makes more sense to be prescriptive on the total you want to include.
Pieces Above Company Target =Var AboveTarget =CALCULATE(CALCULATE(sum(DataTest[Pieces]), FILTER(DataTest, [% Good Pieces] > [Target])),all(DataTest))RETURNIF( CALCULATE([% Good Pieces], ALLEXCEPT(DataTest,DataTest[Company])) > [Target], AboveTarget)ilTac Did I answer your question? Mark my post as a solution