Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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
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.
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
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"
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
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.
@Anonymousplease try this
newTable=Table 2 = GROUPBY(FILTER('Table','Table'[Offer]="won"),'Table'[Customer Name],"sum",SUMX(CURRENTGROUP(),'Table'[Value tot.]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
61 | |
50 | |
45 | |
20 | |
17 |