How do you calculate a field from calculated fields.

[alex1alex]

Hello everyone,

I need to Aggregate a number of multiplications which are based on the Row and Columns. My best attempt at describing this is in pseudo-code.

[FONT=Calibri] 
[/FONT][FONT=Calibri]For each cell in the Pivot table[/FONT]
[FONT=Calibri]    SUM[/FONT]
[FONT=Calibri]       Foreach ORU [/FONT]
[FONT=Calibri]                  Percent= Look up the multiplier for that ORU associated with the Column[/FONT]
[FONT=Calibri]                  SUMofValue = Add all of the Values associated with that Column/Row combination[/FONT]
[FONT=Calibri]                  Multiply Percent * SUMofValue
[/FONT]

I tried a number of ways over the last few days and looked at loads of examples but am missing something.


I have an example XLS here from my onedrive: link
With some screen grabs below.
At the bottom is what I'd like the pivot table to look like.

Any help would really be appreciated.

Kind Regards,
Alex

 

[billszysz]

Hi alex
This is very simple using Power Query.

let
    Charges = Excel.CurrentWorkbook(){[Name="Charges"]}[Content],
    SumByTypeAndORU = Table.Group(Charges, {"Type", "ORU"}, {{"SumType", each List.Sum([Value]), type number}}),
    Orubumgr = Excel.CurrentWorkbook(){[Name="ORUBUMGR"]}[Content],
    JoinTable = Table.NestedJoin(Orubumgr,{"ORU"},SumByTypeAndORU,{"ORU"},"New"),
    Expand = Table.ExpandTableColumn(JoinTable, "New", {"Type", "SumType"}, {"Type", "SumType"}),
    Result = Table.AddColumn(Expand, "Value", each [Percent]*[SumType])
in
    Result

You can add the result of PQ query to your data model or use directly in Pivot Table. You don't need to load the result into the sheet.

Regards

 

[alex1alex]

Hi Bill,

That is pretty cool. I hadn't thought about that approach even though I actually do something like this to create the Percent column in the ORUBUMGR table. :$

I didn't list it in my requirements (since it seemed to be getting too complex) but my Charges table has a bunch of other columns. If I do that 1st group table, I'll loose that ability to slice using those other columns. Also, the Charges table is pretty big ( a million + lines) so with those Join/Expand lines it'll be 100million lines.

ImkeF's solution is working really well/quickly so I'll stick with that.

Thanks very much though, I appreciate the help!
Alex