Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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]
)
Thank you it's worked appreciate your response.
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]
)
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
User | Count |
---|---|
57 | |
21 | |
18 | |
16 | |
12 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |