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
Helper I
Helper 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!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!