cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smore
Frequent Visitor

Sum values based on two conditions.

ABCD

apple

6

1value

TopHalf

apple52valueTopHalf
apple43valueTopHalf
apple34valueBottomHalf
apple25valueBottomHalf
apple16valueBottomHalf
orange37valueTopHalf
orange28valueBOTH
orange19valueBottomHalf

 

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
Orangetophalf/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

@smore 

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)

 

 

1.PNG





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

Proud to be a Super User!




View solution in original post

7 REPLIES 7
ryan_mayu
Super User
Super User

@smore 

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)

1.PNG





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

Proud to be a Super User!




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

@smore 

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)

 

 

1.PNG





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

Proud to be a Super User!




View solution in original post

smore
Frequent Visitor

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

you are welcome





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

Proud to be a Super User!




KeithSayer
Resolver I
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")

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.

smore
Frequent Visitor

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.