Really liked this approach after i'd worked through it a few times, very inventive. I'm not a great fan of pivoting and unpivoting, mainly because i often have no idea what I'm going to end up with.
Interrsting use of Power Query, thanks for the video. Like you said the proper place for adding subtotals etc. is in DAX or on the grid in Excel but a useful tool have at your disposal.
Interesting that you think easier in DAX, I've never been able to insert subs to replicate something like Table.Group(Source, {"Item"}, {{"Count", each let asum = List.Sum(_ [Unit] ) in Table.InsertRows( _, Table.RowCount(_), { [ Item = "SubTotal", Unit = asum ] } ) }} ), Using measures, I know there is the ROLLUPADDSUB, but I've never understood, rollup and not sure it works in excel power pivot, do you have a vid on how to do this?
Is there is any solution for dynamic total for your solution if someone close and load the data then apply filer static total remains un changed or apply any filter after this is the same case too
Really liked this approach after i'd worked through it a few times, very inventive. I'm not a great fan of pivoting and unpivoting, mainly because i often have no idea what I'm going to end up with.
Interrsting use of Power Query, thanks for the video. Like you said the proper place for adding subtotals etc. is in DAX or on the grid in Excel but a useful tool have at your disposal.
awesome thanks Roy! i need to get back into my DAX i feel like i've been neglecting it a bit!
Brilliant
Just tested it in a report - perfect, thanks for the clear explanation :)
Bro, you are genious 😊
thanks man!
Perfect. Thank Roy.
Can we do the same things with pivot table? Thanks 🙏
yes essentially i'd prefer to do it as a pivot table it's easier.
Hi BA Sensei, how can you incorporate the for every total of Portfolio it will skip 1 row or blank row Thanks
Thanks man. In my case, I would need to sum the "Shares" but "Holding" would be a week number, that I want to keep and not to sum... any idea ?
Interesting that you think easier in DAX, I've never been able to insert subs to replicate something like
Table.Group(Source, {"Item"}, {{"Count", each
let asum = List.Sum(_ [Unit] ) in
Table.InsertRows( _, Table.RowCount(_),
{ [ Item = "SubTotal", Unit = asum ] } ) }} ),
Using measures, I know there is the ROLLUPADDSUB, but I've never understood, rollup and not sure it works in excel power pivot, do you have a vid on how to do this?
Is there is any solution for dynamic total for your solution if someone close and load the data then apply filer static total remains un changed or apply any filter after this is the same case too
yes indeed i'll see if i can do it in a future video!
How do I get it to not try to sum dates on total line? Newish to power query and can’t find an answer on google
How to sort total in a top Row sir? Like Total 2024 Total 2023 Total 2022, A 2024 A 2023 A 2022, B 2024 B 2023 .....