Add higher hierarchy level to a column with lower level dimension values

[Yggdrasill]

Can I, with an efficient way, from a dataset do this 

SourceDesired output

Here's the method I used to to this, but I want to be able to do this for 1M+ rows and a lot more dimension values from "Unit" and I'm afraid this method won't do the trick

let
    Source = [Some source file],
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Unit", type text}, {"Amount", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Amount", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Unit]), "Unit", "Amount", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"D1", "D5", "D2", "D3", "D4", "D6"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Total for all units for the day", each [D1]+[D2]+[D3]+[D4]+[D5]+[D6]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Date"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Unit"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Value] <> 0))
in
    #"Filtered Rows1"

Any genius advice out there is appreciated

[v-ljerr-msft]

Hi @Yggdrasill,

May I know why you need to add this total row in your table? You should be able to use the Matrix visual to show the same result on your report. 

Regards

[Yggdrasill]

Thanks for the reply

I have 3 types of end-users with different type of clearance to see the data. I don't want to build 3 reports, just 1 for them all and then apply Row Level Security filters.

Type A can see everything

Type B can see different types of "units" (D1...Dn)

Type C can only see the Totals 

There's probably other way to do this, but I can't seem to figure it out