Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Solved! Go to Solution.
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!
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!
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)
Proud to be a Super User!
Thank you this was extremely helpful! I have marked it as the solution. Cheers!
you are welcome
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |