Cumulative (Running) Totals in DAX

[mcornfield]

Looking to build a Measure that shows the Cumulative % Measure In order by DSC RevNet. I tried multiple Formulas i found but non seem to work.

[Ashish_Mathur]

Hi,

Try these measures

Rank = if([RevNet]=BLANK(),BLANK(),RANKX(ALL('Data'[Name]),[RevNet],,0,SKIP))

Cumulative RevNet = SUMX(TOPN([Rank],CALCULATETABLE(VALUES('Data'[Name]),ALL('Data'[Name])),[RevNet]),[RevNet])

% Measure = divide([Cumulative RevNet],CALCULATE([RevNet],all('Data'[Name])))

Hope this helps.

[mcornfield]

Still getting this error. 

[themistoklis]

@mcornfield 

You can try the following formula:

Cumulative_% = 
VAR _cumul_actual =
    CALCULATE (
        SUM ( 'Table'[RevNet] ),
        FILTER ( ALL ( 'Table' ), 'Table'[RevNet]>= MIN ( 'Table'[RevNet] ) )
    )
VAR _Total =
    CALCULATE ( SUM ( 'Table'[RevNet] ), ALL ( 'Table' ) )
RETURN
    _cumul_actual / _Total

Workspace attached on this message:

[mcornfield]

I tried this but it didnt work. I have a live connection to a tabular model so the Sum() Doesnt work i have to use everything as a measure.

%%Cumulative_% =

VAR _cumul_actual =

CALCULATE (

[Revenue Net Rolling 12],

FILTER ( ALL ( Products ), [Revenue Net Rolling 12]>= MIN ( [Revenue Net Rolling 12] ) )

)

VAR _Total =

CALCULATE ( [Revenue Net Rolling 12] , ALL ( Products ) )

RETURN

_cumul_actual / _Total

[themistoklis]

@mcornfield 

You need to make one correction:

FILTER ( ALL ( Products ), [Revenue Net Rolling 12]>= MIN ( [Revenue Net Rolling 12] ) )

In this filter you should use the field and not the measure (bold statements)

Change to:

FILTER ( ALL ( Products ), 'Products'.Revnet >= MIN ( 'Products'.Revnet ) )

[mcornfield]

its only a measure available. No Fields. Its a live connection to tabular model so i cant do much