Reply
Frequent Visitor
Posts: 17
Registered: ‎09-20-2018
Accepted Solution

PowerBi Desktop - display data as cumulative

[ Edited ]

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? 


Accepted Solutions
Moderator
Posts: 9,487
Registered: ‎03-06-2016

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.

View solution in original post

Attachment

All Replies
Moderator
Posts: 9,487
Registered: ‎03-06-2016

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.
Attachment