Cummulative by Street

[spandy34]

Hi have the following table  that includes a Street Name, No of Properties (which is a measure of the Count of UPRNs) and I want to add a Cummulative Measure for me to add a to a matrix.

Please can someone tell me how I would write the DAX please.

@amitchandak @tamerj1 @goncalogeraldes 

[Martin_D · Accepted solution]

Hi @spandy34 ,

With this measure calculating the number of properties (without cummulation):

# Properties = SUM ( 'Properties by Street'[No of Properties] )

this measure calculates the cummulative number of properties by street:

# Properties cummulating by street = 
VAR _UpToStreet = MAX ( 'Properties by Street'[STREE_NAME] )
RETURN
CALCULATE (
    [# Properties],
    'Properties by Street'[STREE_NAME] <= _UpToStreet
)

Be aware that the order by which is cummulated is defined by the measure, not by the visual. I.e., if you sort the table by a different column, cummulation is not done top to bottom but still by street name alphabetically ascending. If you want to cummulate differently, you need a different measure.
BR
Martin

[spandy34]

Thank you it's worked appreciate your response.

[tamerj1 · Accepted solution]

Hi @spandy34 

please try

Cumulative =
VAR CurrentStreet =
SELECTEDVALUE ( 'Table'[STREET_NAME] )
RETURN
SUMX (
FILTER (
ALLSELECTED ( 'Table'[STREET_NAME] ),
'Table'[STREET_NAME] <= CurrentStreet
),
[No of Properties]
)

[Martin_D · Accepted solution]

Hi @spandy34 ,

With this measure calculating the number of properties (without cummulation):

# Properties = SUM ( 'Properties by Street'[No of Properties] )

this measure calculates the cummulative number of properties by street:

# Properties cummulating by street = 
VAR _UpToStreet = MAX ( 'Properties by Street'[STREE_NAME] )
RETURN
CALCULATE (
    [# Properties],
    'Properties by Street'[STREE_NAME] <= _UpToStreet
)

Be aware that the order by which is cummulated is defined by the measure, not by the visual. I.e., if you sort the table by a different column, cummulation is not done top to bottom but still by street name alphabetically ascending. If you want to cummulate differently, you need a different measure.
BR
Martin