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 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.

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!

7 Replies

  • thedatahiker's avatar
    thedatahiker
    Microsoft Employee
    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
      Frequent Visitor
      5 years ago

      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's avatar
        thedatahiker
        Microsoft Employee
        5 years ago

        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)
  • 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