Forum Discussion
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.
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)
7 Replies
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")
RETURN tophalf/bottomhalf
Hopefully, that helps point you in the right direction. The VAR feature has helped me solve complex calculations like this in the past.
Thanks I will continue trying to break it down step by step using variables.
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)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.
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)
