CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktop
Vinicius Brown
Vinicius Brown

Reputation: 9

Cumulative column for a value with TIME RANGE won't work in Power BI

im new to power bi.

I searched all around but couldn't find an answer to this problem.

I have a column of values that I want to plot two line graphs, first would be a graph of occurrences on a time bin of 30 minutes and the other would be an acumulative during the day.

I can make the time bin part work but I just can't make the acumulative part work on a time column. I tried everything that I could but it only work with dates and not times, so when I put the time values it just repeat the value of the time bin count.

This is what i get:

enter image description here

Cumulative should be [482,819,1092,1349...109381]

This is what I have:

enter image description here

And this is what i need:

enter image description here

Thanks in advance for your time!

Upvotes: 0

Views: 86

Answers (1)

Storax
Storax

Reputation: 12167

I assume you have the following tblData within Power Query as input for the function fnRunningSum (I will add the M-code at the end of the post).

enter image description here

If you use this table as input for fnRunningSum you will get a table with a cumulative sum which I guess you are after enter image description here

The code for fnRunningSum is

(MyTable as table) as table =>
let
    Source = Table.Buffer(MyTable),
    TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
    Cumulative = List.Skip(List.Accumulate(Source[Wert],{0},(cumulative,wert) => cumulative & {List.Last(cumulative) + wert})),
    AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
    AddedRunningSum

PS Another version could be to shorten it to this code just for the input table tbldata

let
    Quelle = tblData,
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(Quelle, "SumList", each List.Skip(List.Accumulate(Quelle[Wert],{0},(cumulative,wert) => cumulative & {List.Last(cumulative) + wert}))),
    #"Hinzugefügter Index" = Table.AddIndexColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Index", 0, 1, Int64.Type),
    #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Hinzugefügter Index", "RunningTotal", each [SumList]{[Index]}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte1",{"SumList", "Index"})
in
    #"Entfernte Spalten"

Upvotes: 1

Related Questions