Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I've a difficult challenge, whereby I need to transform a table by adding calculated rows, allocating 10% of revenue from Category 1 Projects to Category 2. And show this allocation as a separate line.
My data model includes a total of 5 categories (the example below just for illustration).
I want to transform the above original data by adding conditional row that allocates 10% revenue from each Category 1 Project towards Category 2. as below example:
So the final results using above transformed data will reflect this in a Matrix visual:
Solved! Go to Solution.
@Ramiroz
Please find below the Power Query Solution as desired, Copy the code below to a blank query and follow the steps,
You can download the file: HERE
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRck6E0CYGBnoGBkqxOghpIyRpQx0DTAXGUAUghUaY0iZI0oaY0qZIxhtjSpsh68Zmuzl+4y2QjDfDlLZEkjbHlDY0wGJ6LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Category = _t, #"Project Value S" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Value S", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Category] = "Cat 1")),
#"Inserted Multiplication" = Table.AddColumn(#"Filtered Rows", "Cat-1 Allocation", each [Project Value S] * 0.1, type number),
Custom1 = Table.Combine({#"Changed Type", Table.FromRecords({ [Project Name = "Allocation Cat 1", Category = "Cat 1", Project Value S =
-List.Sum(#"Inserted Multiplication"[#"Cat-1 Allocation"])]}),
Table.FromRecords({ [Project Name = "Allocation Cat 2", Category = "Cat 2", Project Value S =
List.Sum(#"Inserted Multiplication"[#"Cat-1 Allocation"])]})
}),
#"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Project Value S", Int64.Type}})
in
#"Changed Type1"
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Ramiroz
peraphs are you after somethink like this?
let
ct = Table.TransformColumnTypes(source,{{"proj", type text}, {"cat", type text}, {"rev", Int64.Type}}),
alloc = (pos)=>
let
r=source[rev]{pos}
in {source{pos}&[proj="c1 allocation",cat="cat1",rev=-r*0.1],source{pos}&[proj="c2 allocation",cat="cat2",rev=r*0.1]},
pos=List.PositionOf(ct[cat],"c1",Occurrence.All),
tt=List.Accumulate(pos,ct,(s,c)=>Table.InsertRows(s,c+List.PositionOf(pos,c)*2+1,alloc(c)))
in
tt
@Ramiroz
Check this solution, hope it is what you were expecting.
You can download the file: HERE
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
let
ct = Table.TransformColumnTypes(source,{{"proj", type text}, {"cat", type text}, {"rev", Int64.Type}}),
alloc = (pos)=>
let
r=source[rev]{pos}
in {source{pos}&[rev=r*0.1],source{pos}&[rev=-r*0.1]},
pos=List.PositionOf(ct[cat],"c1",Occurrence.All),
tt=List.Accumulate(pos,ct,(s,c)=>Table.InsertRows(s,c+List.PositionOf(pos,c)*2+1,alloc(c)))
in
tt
source with lower case starting s is a copy of your table.
this is the allocated table
@Ramiroz
Do you need the solution in Power Query or Data Model (DAX) ?
Hope you have only 2 Categories?
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I want it in Power Query.
Unfortunately I have 5 categories, but only category 1 where I want to allocate 10% of it's revenue to category 2.
@Ramiroz
Please find below the Power Query Solution as desired, Copy the code below to a blank query and follow the steps,
You can download the file: HERE
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRck6E0CYGBnoGBkqxOghpIyRpQx0DTAXGUAUghUaY0iZI0oaY0qZIxhtjSpsh68Zmuzl+4y2QjDfDlLZEkjbHlDY0wGJ6LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, Category = _t, #"Project Value S" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Value S", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Category] = "Cat 1")),
#"Inserted Multiplication" = Table.AddColumn(#"Filtered Rows", "Cat-1 Allocation", each [Project Value S] * 0.1, type number),
Custom1 = Table.Combine({#"Changed Type", Table.FromRecords({ [Project Name = "Allocation Cat 1", Category = "Cat 1", Project Value S =
-List.Sum(#"Inserted Multiplication"[#"Cat-1 Allocation"])]}),
Table.FromRecords({ [Project Name = "Allocation Cat 2", Category = "Cat 2", Project Value S =
List.Sum(#"Inserted Multiplication"[#"Cat-1 Allocation"])]})
}),
#"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"Project Value S", Int64.Type}})
in
#"Changed Type1"
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks @Fowmy I tried it and it's perfect.
The only thing missing is that I don't want to sum/aggregate all the allocations of Cat1, I want to maintain it as a separate record for each project, under unique project name called "Cat1 Allocation", as I want to maintain other columns (ie.Date)
@Ramiroz
Check this solution, hope it is what you were expecting.
You can download the file: HERE
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Ramiroz
peraphs are you after somethink like this?
let
ct = Table.TransformColumnTypes(source,{{"proj", type text}, {"cat", type text}, {"rev", Int64.Type}}),
alloc = (pos)=>
let
r=source[rev]{pos}
in {source{pos}&[proj="c1 allocation",cat="cat1",rev=-r*0.1],source{pos}&[proj="c2 allocation",cat="cat2",rev=r*0.1]},
pos=List.PositionOf(ct[cat],"c1",Occurrence.All),
tt=List.Accumulate(pos,ct,(s,c)=>Table.InsertRows(s,c+List.PositionOf(pos,c)*2+1,alloc(c)))
in
tt
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
8 | |
7 | |
6 |