Divide and Filter by Row condition

[ING_BT]

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 volume B is higher than 500" it will show the others as blank - but it doesn't changes the total value (which should be 5% instead of 1%). 

Note:

• The volume A and the volume B are from different tables.
• They are both linked to a "Letters" table (one to many relationship - as both tables have multiple dimensions).
• The measure devides B by A 
• Both Volume B and A are simple SUM measures

Letters	Volume A	Volume B	Measure (Divide)
A	1,532	576	38%
B	21,656	505	2%
C	2,165	376	17%
D	1,651	125	8%
E	21,516	78	0%
F	161,613	5	0%
Total	210,133	1,665	1%
Only if > 500	23,188	1,081	5%

I hope my example is clear ! 

Thank you for you help!

All the best,

BT

[ING_BT]

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]

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

[v-cgao-msft]

Hi @ING_BT ,

Please try:

Measure = 
IF(
    HASONEVALUE('Table'[Letters]),
    DIVIDE([Sum of Volume B],[Sum of Volume A]),
    VAR _B = 
    SUMX(
        FILTER(
            SUMMARIZE(
                'Table',
                'Table'[Letters],
                "B",[Sum of Volume B]
            ),
            [B]>500
        ),
        [B]
    )
    VAR _A =
    SUMX(
        FILTER(
            SUMMARIZE(
                'Table',
                'Table'[Letters],
                "A",[Sum of Volume A],
                "B",[Sum of Volume B]
            ),
            [B]>500
        ),
        [A]
    )
    RETURN
    DIVIDE(_B,_A)
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[sevenhills]

I am not sure the requirement as it is little confusing. I would like provide below details, so that it help you resolve.

Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY67DcAwCAVXiagp8rDAafNdAnn/NWI5KWIcyuP0dO60EtPUHViTBKrZqLDT1nMBm1p0Z23uPiwLwzTQ9C4fPx2mCBTybJ9DhyJ21MtLs6/vB1aHkUa5llMpNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Letters = _t, #"Volume A" = _t, #"Volume B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Letters", type text}, {"Volume A", Int64.Type}, {"Volume B", Int64.Type}})
in
    #"Changed Type"

Add all measures using Dax:

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

Divide Measure 2 = DIVIDE([SUM B GT 500], [SUM A GT 500], 0.00)

Is GT 500 = IF ( HASONEVALUE('Table GT 500'[Letters]), If ( [Sum B] > 500, 1, 0), BLANK())

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

SUM A GT 500 = 
   -- only for those [Sum B] gt 500
   IF ( HASONEVALUE('Table GT 500'[Letters]), If ( [Sum B] > 500, [Sum A], BLANK()), 
             CALCULATE([Sum A], Filter('Table GT 500', [Sum B] > 500))
    )

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

SUM B GT 500 = 
    IF ( HASONEVALUE('Table GT 500'[Letters]), If ( [Sum B] > 500, [Sum B], BLANK()), 
            CALCULATE([Sum B], Filter('Table GT 500', [Sum B] > 500))
    )

Set the both divide measures format as percentage

Output:

Now, you can refine to your needs! Mark it as answered if it helps you. (and Kudos)