Forum Discussion

ING_BT's avatar
ING_BT
Helper I
3 years ago

Divide and Filter by Row condition

Hello !  I'm trying a simple calculation - I can't quite figure it out   In the example below - I need a measure whith divides the sum ONLY when the ROW SUM is higher than 500.   If I do a "If...
Need Help
ING_BT's avatar
ING_BT
Helper I
3 years ago

Hello ! 

Thank you both for your help ! 


But, I think this doesn't quite apply as here it considers that Volume A and Volume B are in the same table which is not the case: 

TableA[VolumeA] + Column LettersA
TableB[VolumeB] + Column Letters B
TableC[Letters]

Table C connects to TableA and TableB via "Letters" in a One to Many relationship on both. 

I've tried to adapt the measure - 

Thank you again! 

Best,
BT

  • sevenhills's avatar
    sevenhills
    Super User
    3 years ago

    Ok, you would have added sample.

    Try this:

    Add these Measures to corresponding tables like in the model above.

    Sum of Volume A = Sum('Table GT 500 - A'[Volume A])

Sum of Volume B = Sum('Table GT 500 - B'[Volume B])

Measure Divide = DIVIDE([Sum of Volume B], [Sum of Volume A], 0.00)

Measure Divide 2 = 
      var _bTable = FILTER('Table GT 500 - B', [Sum of Volume B] > 500)
      var _b = CALCULATE([Sum of Volume B], _bTable)
      var _a = CALCULATE([Sum of Volume A],  _bTable, all('Table GT 500 - A') )
      RETURN DIVIDE(_b, _a, 0.00)

     

    Output