Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello Altruists,
A Table is given like below. "Week" will be increased or decreased randomly.
Person-- | Week1-- | Week2-- | Week3-- | ... |
Hoover-- | 2-- | 13-- | 30-- | ... |
Franklin-- | 8-- | 3-- | 4-- | ... |
Lincoln-- | 9-- | 9-- | 2-- | ... |
How to let Power BI to display a graph where each week would display sum of values of all previous weeks ?
Such as, Week 3 will display sum of values of Week1+Week2+Week3 , as table below.
Creating new columns would not be an option due to the fact that "week" row will be increased or decreased.
Person-- | Week1-- | Week2+All previous week's value-- | Week3 +All previous week's value-- | ... |
Hoover-- | 2-- | 15-- | 45-- | ... |
Franklin-- | 8-- | 11-- | 15-- | ... |
Lincoln-- | 9-- | 18-- | 20-- | ... |
Can someone help please?
Thank you in advance.
hello , @sparvez
you need to unpivot your table from power query first.
then you need a cumulative measure :
try using the following :
calculate (
sum( table[col] ,
all(dimdate) ,
dimdate[date] <= max(dimdate[date])
)
or you can use window function :
hope this is what you are looking for.
best regards
Hi, I get error, can u pls upload the power BI file ? thanks
Hi, sorry, failed to open it, may be version issue. Could you please copy paste here the entire text of "Advanced Editor" ? Thank you again
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sjPL0stUtJRMgJiQ2MgYWygFKsTreRWlJiXnZOZBxSxAIkCsQlYwiczLzk/ByRuCcVGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Week1 = _t, Week2 = _t, Week3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Week1", Int64.Type}, {"Week2", Int64.Type}, {"Week3", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Person"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "week nb"}})
in
#"Renamed Columns"
change the name of the table to your table name.
or you can do it by clicking on the 3 columns, and select unpivot table.
if you have many columns that you want to pivot,
you can select the first column, and click unpivot other columns,
Hi, in this "entire advanced editor" text you posted in last reply, text that you have mentioned in first reply ( below image) is missing. I am lost
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |