Aggregation over facility

Hi lg01 ,

To calculate the total water pump capacity per facility, you can use a DAX formula to either create a calculated column or a measure. A calculated column will display the total capacity per facility for each row in your table, while a measure dynamically calculates the value in visuals without adding to the data model size.

For the calculated column, you can use the following DAX formula:

Capacity Per Facility (gal) = 
CALCULATE(
    SUM('Sample Table'[water_pump_capacity (gal)]),
    ALLEXCEPT('Sample Table', 'Sample Table'[facility_id])
)

This formula sums up the water pump capacities for all rows grouped by the facility_id. The ALLEXCEPT function ensures that only the facility_id filter is retained, providing the correct aggregation of pump capacities within each facility.

Alternatively, if you prefer a dynamic approach using a measure, the DAX formula can be written as:

Total Facility Capacity = 
SUMX(
    VALUES('Sample Table'[water_pump_id]),
    CALCULATE(
        MAX('Sample Table'[water_pump_capacity (gal)])
    )
)

This measure dynamically calculates the total capacity by iterating over each unique water pump within the facility using the VALUES function, ensuring no duplication of pump capacities. The MAX function ensures that the correct capacity for each pump is considered, and SUMX aggregates these values for the entire facility.

Using either approach, the total capacity for the facility with facility_id = 727e274d067b48c3bd4adfddb99b9175 will correctly return 8000 gallons (5000 + 3000). Let me know if further clarification is needed!

Best regards,