Forum Discussion

ilTac's avatar
ilTac
Frequent Visitor
5 years ago
Solved

SUM based on specific condition

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...
Need Help
  • thedatahiker's avatar
    thedatahiker
    5 years ago

    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's avatar
    ilTac
    5 years ago

    thedatahiker 

    yes!

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

     

     

    Thank you for your support!

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
ilTac
Frequent Visitor
5 years ago

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