Merge values Power BI DAX

[Anonymous]

Hello all, 

I have one datasate that is structured as: 

Customer Name	Value tot.	Offer
Client 1	x $	won
Client 2	y $	lost
Client 1,...	....	hold,...
Client n	z $	received

I would like to create a new table that group all the clients per name, and return a new table in which I have their total monetary value and the total monetary value in which I won the offer, 

How can i do it? 
I have tried many solutions but it always says to me:"Impossible to do it with PowerBY DAX" 

Do someone knows a solution for me?   THANKS in advance 🙂 

(Watch Out: the same client is repeated many times inside the document... This is the main problem)  

[Jimmy801 · Accepted solution]

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

[Jimmy801 · Accepted solution]

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

[Anonymous]

Hello Jimmy, thanks a lot but I was already able to do it in this way. 

Unfortunately, still I have the following message: 

"..This is not supported on the DirectQuery mode" 

[Jimmy801]

Hello @Anonymous 

I didn't know that your are using DirectQuery. With DirectQuery you are very limited in data manipulation. As @v-yingjl  was writing you have to change to import mode and load your data in your dataset or you are following his instructions

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

[v-yingjl · Accepted solution]

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.

[smpa01]

@Anonymousplease try this

newTable=Table 2 = GROUPBY(FILTER('Table','Table'[Offer]="won"),'Table'[Customer Name],"sum",SUMX(CURRENTGROUP(),'Table'[Value tot.]))