cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

Cumulative (Running) Totals in DAX

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.

6 REPLIES 6
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

Still getting this error.

Community Champion

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:

Helper III

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
Community Champion

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 ) )

Helper III

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