Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |