CODEWITHSUNDEEP
powerbidaxpowerquery
WrittenInCode
WrittenInCode

Reputation: 51

Running / Cumulative Total in DAX using only measures, no calculated columns

Problem:

Create a running total measure that does not reference any calculated columns, only measures.

One measure is a calculation. Another measure is auto-incrementing IDs created from a RANKX.

The goal is to create a running total measure/sum for A using reference only to B.

Context: For data restriction reasons, there is no ability to add a calculated column. Therefore, there are a number of measures that have been created.

Existing trials:

Existing knowledge base (internet searches) only seem to refer to measures where there is a mixture of columns and measures. There is no ability to add calculated columns, however, nor need for/use of existing columns within this particular running total.

Currently there has been an attempt to use a table expression to build the table with the measures, creating additional variables for a max (MAXX) and trying to filter this way and use a CAlCULATE in the return. This is only returning the total and not a running total.

Edit:

Example table:

[...existing cols] Measure A Measure B (Needed) Measure C
... 10 1 10
... 60 2 70
... 40 3 110

Measure A is a sum of other measures Measure B is a rank of other measures Measure C is a running total of measure A given Measure B

Upvotes: 2

Views: 3528

Answers (2)

r.adler
r.adler

Reputation: 1

Perhaps you find it useful avoiding the prior step of creating an independent measure for MeasureA:

Cummulative MeasureA=
VAR _CurrentRank=[MeasureB]
VAR _SomeTable=ADDCOLUMNS(FILTER(ALL(DataTable), [MeasureB]<=_CurrentRank),"Measure A Value",...whatever the formula for MeasureA should be, not the measure itself...)
RETURN 
SUMX(_SomeTable, [Measure A Value])

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40244

The basic pattern for this is:

CumulativeMeasureA =
VAR CurrentRank = [MeasureB]
RETURN
    SUMX ( FILTER ( ALL ( Data[Group] ), [MeasureB] <= CurrentRank ), [MeasureA] )

Where Data[Group] is the column that you are grouping on in your report visual.

Note that this isn't very efficient without calculated columns since it is having to compute [MeasureB] for every Group for every row in your visual, filter those results, and compute [MeasureA] every iteration that isn't filtered out. For small datasets, this isn't a problem but can be as things get larger and more complex.

Upvotes: 5

Related Questions