Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Can I, with an efficient way, from a dataset do this
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
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
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
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |