Display Sum

[sparvez]

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.

[Daniel29195]

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 : 

Measure 11 =

CALCULATE(

    SUM('Table (13)'[Value]),

    CALCULATETABLE(

    WINDOW(

        0,

        ABS,

        1,

        SUMMARIZE(

            'Table (13)',

            'Table (13)'[Index],

            'Table (13)'[Person],

            'Table (13)'[week nb]

        ),

        ORDERBY('Table (13)'[week nb] ,  asc)

    ),

    all('Table (13)'[week nb]),

    'Table (13)'[week nb] <= MAX('Table (13)'[week nb])

))

hope this is what you are looking for. 

best regards

[sparvez]

Hi, I get error, can u pls upload the power BI file ? thanks

[Daniel29195]

https://drive.google.com/file/d/1xvnY-PkYk0i0ZNC_rP39qYxwfntT2pwe/view?usp=sharing 

@sparvez 

[sparvez]

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

[Daniel29195]

@sparvez 

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, 

[sparvez]

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