Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smore
Helper I
Helper I

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!




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!




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!




Anonymous
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.