Forum Discussion

lg01's avatar
lg01
Advocate III
1 year ago
Solved

Aggregation over facility

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 cap...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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:

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.