topic Re: Summing measures in DAX Commands and Tips

Summing measures

tomascid — Tue, 28 Mar 2023 05:56:51 GMT

Hi there - I've created a table visual like that on the left. 'Result' is a measure. I'd like to create a 2nd table where I'm summing the result per facility. But I can't see to figure out what DAX to use - SUM() is only for tables, not measures. Do I need some sort of custom script?

Facility	Result	Facility	Sum of Result
A	0.17	A	1.96
A	0.75	B	2.77
A	0.25	C	1.88
A	0.78
B	0.62
B	0.94
B	0.30
B	0.91
C	0.29
C	0.34
C	0.33
C	0.92

Re: Summing measures

johnt75 — Tue, 28 Mar 2023 08:21:00 GMT

You can try

Sum of result = SUMX ( VALUES ( 'Table'[Facility] ), [Result] )

Re: Summing measures

tomascid — Wed, 29 Mar 2023 20:28:06 GMT

The issue is that the table on the left is not a PBI data table, it's a table visual I've generated on-the-fly. 'Facility' comes from a table but 'Result' is itself a measure.

Re: Summing measures

johnt75 — Thu, 30 Mar 2023 08:16:29 GMT

The principal will still work, although you will need to include all the columns from the table visual which uniquely identify a row. In your example there is only facility, but there must be more columns otherwise each row would give the same result.

If you were grouping by facility and month for example you could use

Sum of result = SUMX ( SUMMARIZE ( 'Table', 'Table'[Facility], 'Table'[Month] ), [Result] )

which will calculate [Result] for each combination of facility and month and then sum them up.