Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Tasha_DGS
Frequent Visitor

Issue with grouping percentages based on years

Hi All,

I have a power query that calculates the total weight of a product we have delivered in a 12 month period. 

I have calculated the total weight of all products delivered over 3 seperate financial years and the corresponding percentage of each product line for each year, however after removing the year from the query it is still seperating the items as if there was a year column. The calculation I used to get the percentage for each year is, just changing the Fiscal year for each percentage:

 "21/22 % of Total Weight", each if [Calendar.Fiscal Year] = "2022" then [Percent Of] else " ")

The image below is what I am getting, but I need to have 1 line for each item code.

Can someone let me know what I am doing wrong please!! 

Tasha_DGS_0-1715877367603.png

 

2 REPLIES 2
PhilipTreacy
Super User
Super User

Hi @Tasha_DGS 

 

What exactly is the final desired result?  Do you want to keep rows that have a 0 % value?

 

In other words do you want this

 

this.png

 

or this

 

orthis.png

 

 

If you want the first one, right click the Item Code column header then select Unpivot Other Columns. You can the delete the Attribute column and filter out 0 values from the Value column to leave you with the final result.

 

Here's the query

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvKPcHIMdlXSUcorzckBUgZ6BoYwXqwOWIGPpwuKvAGQAsu5RoQEOQb7Ovr4YJf3cXfVBeo3RshCFBjjkEXW6+vqQpIsRIEZsqwJmqwhDklkg4N9fUCyhtidjCmLRa8RsbIQBSZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Code" = _t, #"21/22 % Total Weight" = _t, #"22/23 % Total Weight" = _t, #"23/24 % Total Weight" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Code", type text}, {"21/22 % Total Weight", Percentage.Type}, {"22/23 % Total Weight", Percentage.Type}, {"23/24 % Total Weight", Percentage.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item Code"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> 0))
in
    #"Filtered Rows"

 

 

If you want to keep items with a 0% value, Unpivot the other columns as in the first example, but then Group the Item Code and select Max as the aggregation

 

grpby.png

 

Here's the query

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvKPcHIMdlXSUcorzckBUgZ6BoYwXqwOWIGPpwuKvAGQAsu5RoQEOQb7Ovr4YJf3cXfVBeo3RshCFBjjkEXW6+vqQpIsRIEZsqwJmqwhDklkg4N9fUCyhtidjCmLRa8RsbIQBSZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Code" = _t, #"21/22 % Total Weight" = _t, #"22/23 % Total Weight" = _t, #"23/24 % Total Weight" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Code", type text}, {"21/22 % Total Weight", Percentage.Type}, {"22/23 % Total Weight", Percentage.Type}, {"23/24 % Total Weight", Percentage.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Item Code"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Item Code"}, {{"% of Total Weight", each List.Max([Value]), type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"% of Total Weight", Percentage.Type}})
in
    #"Changed Type1"

 

 

Both examples are in this PBIX file

 

Regards

 

Phil

 

 

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi Phil, 

Thanks for this, it's not quite the result I'm after, probably the way I've explained it sorry!

 

I need to keep all three percentage columns in the table as they are needed for appotioning other values but I need to have only 1 Item code in the list.

 

So that it looks like the below if the item has percentages against more than 1 year, rather than a line for each year:

 

Tasha_DGS_0-1715935683119.png

Hope that makes sense!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.