Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
How can I use SUMIF in Table.Group, i,e I want to group the data conditionally.Below is the sample data and output I need:
Group | Sales | Flag |
A | 1000 | 1 |
A | 500 | 0 |
A | 1000 | 1 |
B | 1400 | 1 |
B | 1600 | 1 |
C | 5000 | 1 |
For this data, I need sum of sales by Group where Flag is 1;
Output I need is:
Group | Sales | Flag |
A | 2000 | 1 |
B | 3000 | 1 |
C | 5000 | 1 |
We have something like this
= Table.Group(Rategain_PivotN,"Join_key",{"AvgCheapestPrice-EZ", each List.SUM([Cheapest Price])})
But I need something which will allow me to group data by join _key and sum based of flag=1.So is there a function called List.Sumif in power query where I can use it in Table.group.
Regards
Lavanya
Hi @lavanyak,
Based on my test, we can use Group By feature in power query. Firstly, we need filter the table, after that we can use the group By function to get the result as below.
The backend M formula is below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MDAAUUqxOhABUzDfAM5HUeAEYpmgC5ghCThDjIAKxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Group " = _t, Sales = _t, Flag = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group ", type text}, {"Sales", Int64.Type}, {"Flag", Int64.Type}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Flag] = 1)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Group "}, {{"Count", each List.Sum([Sales]), type number}, {"flag", each List.Max([Flag]), type number}}) in #"Grouped Rows"
For more details, please check the pbix as attached.
https://www.dropbox.com/s/om76kw1yrejkjl4/Sumif%20in%20Table.group%20power%20query.pbix?dl=0
Regards,
Frank
Thanks for your response.
I need something like List.SUMIF, because for few fields I need total sum irrespective of whatever value flag takes and few other fields I need sum where flag is 1.
If I filter the records bfr grouping then I wont be able to get sum of all values where flag is 1,2,3 etc.
Hi @lavanyak,
Here we can add a conditional column in advance to get all the values of flag you need.
= Table.AddColumn(#"Changed Type", "Custom", each if [Flag] <> 0 then [Sales] else null)
After that, we can we can use Group By function as before.
The backend M formula is below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MDAAUUqxOhABUzDfAM6HKjACCziBBEyQdIAFzJBUOEOMgKqIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Group " = _t, Sales = _t, Flag = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group ", type text}, {"Sales", Int64.Type}, {"Flag", Int64.Type}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Flag] <> 0 then [Sales] else null), #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Group "}, {{"Count", each List.Sum([Sales]), type number}, {"asd", each _, type table}}) in #"Grouped Rows"
For more details, please check the pbix as attached.
https://www.dropbox.com/s/f9gcjkbtt7r5zhy/Sumif%20in%20Table.group%20power%20query2.pbix?dl=0
Regards,
Frank
Hi @lavanyak,
Does that make sense? If so , kindly mark the answer as a solution to close the case, thanks in advance.
Regards,
Frank
Could you simply filter out anything with FLAG = 0 on the previous statement, then use LIST.SUM?
Hi @lavanyak,
In Power Query you should first filter the Flag for values equal 1 and then make the group by Group.
This will give the expected result.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |