How do I get my DAX measure to calculate grouped values?

2
votes

I need help with a DAX measure in my Power BI report. I am just learning about measures, so sorry if this seems like a newbie question.

Here’s my scenario:

The purpose of the report is to show water usage for various locations in a municipality. The data is coming from IOT sensors, with new entries every few minutes or so.

A sample of the data generally looks like this:

|SensorID  |Timestamp            |Reading
|----------|---------------------|--------
|1         |2017-06-22 12:01 AM  |123.45
|1         |2017-06-22 12:15 AM  |124.56
|1         |2017-06-22 12:36 AM  |128.38
|2         |2017-06-22 02:12 AM  |564.75
|2         |2017-06-22 02:43 AM  |581.97

I have a simple DAX measure that I use to calculate water usage for each location/sensor, for the current date range selected in the report (via Timeline control):

Usage:= (MAX([Reading]) - MIN([Reading]))

This measure works great when a single location/sensor is selected. However, when I select multiple locations, the calculation is incorrect. It takes the MAX value from ALL sensors, and subtracts the MIN value from ALL sensors - rather than calculating the usage from each location and then summing the usage.

For example, given the data sample above, the correct calculation should be:

Total Usage = (128.38 - 123.45) + (581.97 - 564.75) = 22.15

Instead, it is calculating it this way:

Total Usage = (581.97 - 123.45) = 458.52

How can I get the measure to calculate the usage, grouped by the Sensor ID?

I hope this makes sense.

Thanks!

1
powerbidax

1 Answers

2
votes

Try this:

Total Usage:= SUMX( VALUES(MyTable[SensorID]), [Usage])

VALUES(MyTable[SensorID]) function gives a list of unique SensorIDs. SUMX function then goes over that list one by one and calculates your [Usage] measure per SensorID. Then it sums up the results.

An alternative solution:

Total Usage:= SUMX( SUMMARIZE(MyTable, MyTable[SensorID]), [Usage])

It works the same way, only the list of unique sensor ids is returned by SUMMARIZE function instead of VALUES.

Results:

Results: