Search code examples
powerbireportingpowerbi-desktoppower-bi-report-server

Power BI Running total with Multiple colums and no date column


I have the data below from sql

    create table #tbl ([Group] varchar(10), [year] int, scenerio varchar(10), Total money)
    insert into #tbl values
    ('Group1',2010,'Up',5),('Group1',2010,'Up',7),('Group3',2012,'Back',4),
    ('Group2',2016,'Front',10),('Group2',2014,'Back',12),('Group1',2015,'Front',7),
    ('Group2',2016,'base',10),('Group1',2017,'Up',11),('Group1',2018,'Up',10),
    ('Group3',2020,'Middle',5),('Group1',2020,'base',6),('Group1',2015,'Back',8),
    ('Group1',2015,'Back',2),('Group3',2020,'Middle',12)
    select * from #tbl
drop table #tbl

in power BI i created a DAX for total SUM(Data[Total] and for Running total

Running Total = CALCULATE(
                 sum(Data[Total]),
              ALLSELECTED(data),Data[year]<= MAX(Data[year]))
 current Output

enter image description here

Desired Output

enter image description here


Solution

  • Perhaps:

    Running Total =
    CALCULATE(
        SUM( Data[Total] ),
        FILTER(
            ALLSELECTED( Data ),
            Data[Group] & "|" & Data[year] & "|" & Data[scenerio]
                <= MIN( Data[Group] ) & "|"
                    & MIN( Data[year] ) & "|"
                    & MIN( Data[scenerio] )
        )
    )