topic Re: Cummulative by Street in DAX Commands and Tips

Cummulative by Street

spandy34 — Tue, 27 Jun 2023 17:57:52 GMT

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

Re: Cummulative by Street

Martin_D — Tue, 27 Jun 2023 18:33:49 GMT

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

Re: Cummulative by Street

tamerj1 — Tue, 27 Jun 2023 18:42:35 GMT

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

Re: Cummulative by Street

spandy34 — Tue, 27 Jun 2023 21:07:07 GMT

Thank you it's worked appreciate your response.