cancel
Showing results for
Did you mean:
Frequent Visitor

## Sum values based on two conditions.

 A B C D apple 6 1value TopHalf apple 5 2value TopHalf apple 4 3value TopHalf apple 3 4value BottomHalf apple 2 5value BottomHalf apple 1 6value BottomHalf orange 3 7value TopHalf orange 2 8value BOTH orange 1 9value BottomHalf

I have a table with 3 columns, A, B and C. Column D is added for clarification. I am trying to sum the Values in Column C based on two conditions and then find the ratio of the sums.

Desired result:

 Apple tophalf/bottomhalf ratio Orange tophalf/bottomhalf ratio

Two conditions:

• whether they are in the Tophalf or BottomHalf of the max value in Column B
• Belong in same Group from Column A.

For example, the max of apple in column B is 6 and the max of orange is 3. So I want the tophalf & bottom half sums for each respective group.

Example: For apple
TopHalfSum = 1value + 2 value + 3value
BottomHalfSum= 4value + 5value + 6value

For orange:

TophalfSum = 7value + 0.5*(8value)

BottomHalfSum = 0.5*(8value) + 9value

I am struggling to figure out what is the best workflow to achieve the desired output result. I have tried IF statements, HASONEVALUE and MAX functions in DAX columns and have been unsuccessful.

1 ACCEPTED SOLUTION
Super User

does that mean you want to create calculated column?

``````Column =
VAR _count=CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[A]))
VAR _max=CALCULATE(max('Table'[B]),ALLEXCEPT('Table','Table'[A]))
VAR _type=mod(_count,2)
return if(_type=0,if('Table'[B]>_max/2,"TopHalf","BottomHalf"),if('Table'[B]>roundup(_max/2,0),"TopHalf",if('Table'[B]=roundup(_max/2,0),"BOTH","BottomHalf")))

Column 2 =
VAR _top=CALCULATE(sum('Table'[C]),FILTER('Table','Table'[A]=EARLIER('Table'[A])&&'Table'[Column]="TopHalf"))
VAR _bottom=CALCULATE(sum('Table'[C]),FILTER('Table','Table'[A]=EARLIER('Table'[A])&&'Table'[Column]="BottomHalf"))
VAR _both=CALCULATE(sum('Table'[C]),FILTER('Table','Table'[A]=EARLIER('Table'[A])&&'Table'[Column]="BOTH"))/2
return DIVIDE(_top+_both,_bottom+_both)``````

Proud to be a Super User!

7 REPLIES 7
Super User

pls try this

``````Measure =
VAR _top=CALCULATE(sum('Table'[C]),FILTER('Table','Table'[D]="TopHalf"))
VAR _bottom=CALCULATE(sum('Table'[C]),FILTER('Table','Table'[D]="BottomHalf"))
VAR _both=CALCULATE(sum('Table'[C]),FILTER('Table','Table'[D]="BOTH"))/2
return DIVIDE(_top+_both,_bottom+_both)``````

Proud to be a Super User!

Frequent Visitor

Column D was provided only for clarification and does not exist in the table. I need the ratio of tophalf/bottom half for each respective group in Column A, not overall. Thanks for your reply.

Super User

does that mean you want to create calculated column?

``````Column =
VAR _count=CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[A]))
VAR _max=CALCULATE(max('Table'[B]),ALLEXCEPT('Table','Table'[A]))
VAR _type=mod(_count,2)
return if(_type=0,if('Table'[B]>_max/2,"TopHalf","BottomHalf"),if('Table'[B]>roundup(_max/2,0),"TopHalf",if('Table'[B]=roundup(_max/2,0),"BOTH","BottomHalf")))

Column 2 =
VAR _top=CALCULATE(sum('Table'[C]),FILTER('Table','Table'[A]=EARLIER('Table'[A])&&'Table'[Column]="TopHalf"))
VAR _bottom=CALCULATE(sum('Table'[C]),FILTER('Table','Table'[A]=EARLIER('Table'[A])&&'Table'[Column]="BottomHalf"))
VAR _both=CALCULATE(sum('Table'[C]),FILTER('Table','Table'[A]=EARLIER('Table'[A])&&'Table'[Column]="BOTH"))/2
return DIVIDE(_top+_both,_bottom+_both)``````

Proud to be a Super User!

Frequent Visitor

Thank you this was extremely helpful! I have marked it as the solution. Cheers!

Super User

you are welcome

Proud to be a Super User!

Resolver I

This is a pretty complex calculation.

I would highly recommend breaking it down into variables to keep everything straight.

I don't really have the time to run through the calculations exactly but I would recommend doing something like:

Measure Apple =
VAR tophalf = calculate( ... ..., filter(A="apple")

VAR bottomhalf = calculate( ... ..., filter(A="apple")

Hopefully, that helps point you in the right direction. The VAR feature has helped me solve complex calculations like this in the past.

Frequent Visitor

Thanks I will continue trying to break it down step by step using variables.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!