cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
aznariy Frequent Visitor
Frequent Visitor

PowerBi Desktop - display data as cumulative

Hello, 

 

Seems like I implemented the formula from here https://community.powerbi.com/t5/Desktop/Cumulative-Count-by-Date/td-p/368918 but that didn't work. 

 

I'm trying to display my severities by month cumulative: in other words add value of the previous cell in each next cell of every row. Please refer to the picture below, where values are not adding to each other, rather staying there as standalones, and only sum up at the most last column called Total. 

 

PowerBi_problem.png

 

So far I've tried these formulas:

 

CumulativeSeverity = 
CALCULATE (
SUM ( Table[Month-Year] ),
FILTER (
ALLSELECTED( Table[Month-Year] ),
YEAR( Table[Month-Year] ) = YEAR ( MAX ( Table[Month-Year] ) )
&& Table[Month-Year] <= MAX ( Table[Month-Year] )
)
)

Getting this interesting message

PowerBi_problem.png

 

Not sure what String PowerBi is complaining about, in the entire formula I only use Date formats

 

Another try was

 

CumulativeSeverity = CALCULATE(
COUNTA(Table[Month-Year]),
FILTER(ALL(Table), Table[Month-Year] <= MAX(Table[Month-Year])), VALUES(Table[Severity]))

 

Is this ever possible in PowerBi Desktop to calculate rows cumulatevely? 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: PowerBi Desktop - display data as cumulative

Hi @aznariy,

 

The column Table[Month-Year] data type isn't number, so the error occurs when you use SUM ( Table[Month-Year] ). 

 

For your requirement, you can modify the measure like below: 

 

CumulativeSeverity = 
IF (
    ISINSCOPE ( 'Table'[Month-Year] ),
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( 'Table'[Month-Year] ) = YEAR ( MAX ( 'Table'[Month-Year] ) )
                && 'Table'[Month-Year] <= MAX ( 'Table'[Month-Year] )
                && 'Table'[Severity] = MAX ( 'Table'[Severity] )
        )
    ),
    SUM ( 'Table'[Amount] )
)

w2.PNG

 

You can download the attached pbix file to test it. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Moderator v-qiuyu-msft
Moderator

Re: PowerBi Desktop - display data as cumulative

Hi @aznariy,

 

The column Table[Month-Year] data type isn't number, so the error occurs when you use SUM ( Table[Month-Year] ). 

 

For your requirement, you can modify the measure like below: 

 

CumulativeSeverity = 
IF (
    ISINSCOPE ( 'Table'[Month-Year] ),
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( 'Table'[Month-Year] ) = YEAR ( MAX ( 'Table'[Month-Year] ) )
                && 'Table'[Month-Year] <= MAX ( 'Table'[Month-Year] )
                && 'Table'[Severity] = MAX ( 'Table'[Severity] )
        )
    ),
    SUM ( 'Table'[Amount] )
)

w2.PNG

 

You can download the attached pbix file to test it. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.