cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Yggdrasill Member
Member

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

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

total from this.PNGSourcetotal to this.PNGDesired 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

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

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

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. Smiley Happy

 

r3.PNG

 

Regards

Yggdrasill Member
Member

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

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