SUM based on specific condition

[ilTac]

Hello everyone, 

i've been trying on this for a while and it seems i'm not able to do it.

I think it should be simple but anyway:

here's my database:

I would like to see % of good pieces so i made this measure:

%GOOD PIECE = sum(Good Pieces)/sum(pieces)

and this is what i have when i put everything in a visual (table).

now below what i want to achieve (the 2 additional columns)

basically i would like to show to sum of all PIECES of ALL COMPANY below and above target.

i can't seem to figure this out.

I'm trying:

measure = calculate( sum(pieces), all(company), table([target])<%Good Pieces))

but it is not working,

can anyone help me?

Thank you so much.

[thedatahiker · Accepted solution]

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)

)

RETURN

IF( [% Good Pieces] < [Target], BelowTarget)

Pieces Above Company Target :=

Var AboveTarget =

CALCULATE(

CALCULATE(sum(DataTest[Pieces]), FILTER(DataTest, [% Good Pieces] > [Target])),

all(DataTest)

)

RETURN

IF( [% Good Pieces] > [Target], AboveTarget)

[thedatahiker · Accepted solution]

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)

)

RETURN

IF( [% Good Pieces] < [Target], BelowTarget)

Pieces Above Company Target :=

Var AboveTarget =

CALCULATE(

CALCULATE(sum(DataTest[Pieces]), FILTER(DataTest, [% Good Pieces] > [Target])),

all(DataTest)

)

RETURN

IF( [% Good Pieces] > [Target], AboveTarget)

[ilTac]

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

[thedatahiker]

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)

)

RETURN

IF( CALCULATE([% Good Pieces], ALLEXCEPT(DataTest,DataTest[Company])) > [Target], AboveTarget)

[thedatahiker]

@ilTac Did I answer your question? Mark my post as a solution

[ilTac · Accepted solution]

@thedatahiker 

yes!

I just needed to add the filter in the Variable too.

Thank you for your support!

[amitchandak]

@ilTac , Try a measure like

below = calculate( sumX(filter(values(Table[company]), Sum([target])< [%Good Pieces]), calculate(sum(Table[pieces]))))

Above = calculate( sumX(filter(values(Table[company]), Sum([target])> [%Good Pieces]), calculate(sum(Table[pieces]))))

[ilTac]

@amitchandak thank you for the answer.

This is the first try:

basically it gives me te same value without aggregation and it considers everything above the target.

so i tryed this (using MAX instead of SUM)

but it doesn't aggregate. So i tryed to add another filter to the sum function:

but it gives me the sum for all the company