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
sevenhills's avatar
sevenhills
Super User
3 years ago

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)