Merge values Power BI DAX

Hello Anonymous 

 

if you need a solution in PowerQuery (you are using in power query forum after all 😉) you can use Table.Group with 2 functions. Here the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7JTM0rUTBUUNJRMgTi8vw8pVgduLgRUAiEc/KLS5DFweqNgTgjPycFXYMJEBelJqdmlqWmYGgyxaHJDGZ5LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, #"Value tot." = _t, Offer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"Value tot.", Int64.Type}, {"Offer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Name"}, {{"SumValueTot", each List.Sum([#"Value tot."]), type number}, {"SumValueTotWon", each List.Sum(Table.SelectRows(_, each [Offer]="won") [#"Value tot."]), type number}})
in
    #"Grouped Rows"

transforms this

 

into this

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

Hi Anonymous ,

Under Direct Query mode, there are some limitations on transforming data in Power Query and calculations on DAX.

Please refer this document first: Implications of using DirectQuery 

If you want to use Jimmy's workaround, you need to switch the connection mode to Import.

If you want to use DAX to achieve this, you can create a calculated table like this but the connection mode would be changed to mixed.

New Table = 
SUMMARIZE (
    'Customers',
    'Customers'[Customer Name],
    "Total sum", SUM ( 'Customers'[Value tot.] ),
    "Won sum", CALCULATE ( SUM ( 'Customers'[Value tot.] ), 'Customers'[Offer] = "won" )
)

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.