Aggregation over facility

[lg01]

See sample below. I have a table with data from different facilities. These facilities have multiple "production tables" and they also have special equipments (water pumps) with a defined gallons capacity. I am trying to calculate the total gallons capacity per facility, based on the capacity of each water pump in the facility. For instance, in the sample below  there is one facility, with two pumps, one has capacity of 5000 gallons the other has a capacity of 3000. So my calculated column "Capacity Per Facility" should report 8000 gal total capacity for the facility. So far I have tried multiple things. The last, shown below, I tried with filters and it gave me a very high number as it was aggregating each line per water_pump_ID.

CALCULATE(SUM('Sample Table'[water_pump_capacity (gal)]),ALLEXCEPT('Sample Table','Sample Table'[water_pump_id], 'Sample Table'[facility_id]))

The filter though should group by  water pump id and aggregate over the facility. So in this case, it should have aggregated 5000+3000 for the total of 8000 as shown below in the sample

facility_id	water_pump_id	water_pump_capacity (gal)	Production Table	Capacity Per Facility (gal)
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 1	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 2	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 3	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 4	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 5	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 6	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 7	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 8	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 9	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 10	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 11	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 12	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 13	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 14	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 15	8000
727e274d067b48c3bd4adfddb99b9175	f05ef71dc1744fd4ad3264430fd62de8	3000	Table 16	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 17	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 18	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 19	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 20	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 21	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 22	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 23	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 24	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 25	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 26	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 27	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 28	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 29	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 30	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 31	8000
727e274d067b48c3bd4adfddb99b9175	390bb47dbc6f45d3a6e0e60adbc428b2	5000	Table 32	8000

Any ideas would be appreciated 

[lg01]

Thank you for all your replies and the effort. Much appreciated. 

[Bibiano_Geraldo · Accepted solution]

Hi @lg01 ,

You can achieve your goal by this DAX calculated column:

Capacity Per Facility (gal) = 
CALCULATE (
    SUMX (
        VALUES ( 'Sample Table'[water_pump_id] ), 
        FIRSTNONBLANK ( 'Sample Table'[water_pump_capacity (gal)], 0 )
    ),
    ALLEXCEPT ( 'Sample Table', 'Sample Table'[facility_id] )
)

Now your table will look like this:

[v-denglli-msft]

Thanks for the reply from DataNinja777 and Ashish_Mathur, please allow me to provide another insight.
Hi @lg01 ,

Please try the following DAX.

Capacity Per Facility (gal) = 
VAR _tb =
    SUMMARIZECOLUMNS (
        'Table'[facility_id],
        'Table'[water_pump_id],
        'Table'[water_pump_capacity (gal)]
    )
RETURN
    SUMX ( _tb, [water_pump_capacity (gal)] )

 The SUMMARIZECOLUMNS function creates a virtual table grouped by water_pump_id.
Then use the SUMX function to aggregate water_pump_capacity.

The final result is as follows. Hopefully it will meet your needs.

Please see the attahced pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[DataNinja777]

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,