Sum rows of a 2 columns that have a value in 1

[Anonymous]

Actual Units	Ceiling Vol
2	500
3	600
0	100
0	200
5	600

Actual Unit Utilization = SUM(Table1[Actual Units]) / SUM(Table1[Ceiling Vol]) * 100

I have tha above formula that takes sum of actual units and ceiling vol, and then times by 100 to get a percentage to basically find the utilization. However I want to find a way so that I only sum ceiling vol rows that also have a actual unit on the same row that isnt 0.

E.g. in above table, rows 3 and 4 the ceiling volume should not be summed.

So it should be ((2+3+5) / (500+600+600)) * 100

[Ashish_Mathur]

Hi,

Try these measures

A_units = sum(Table[Actual units])

C_Vol = calculate(sum(Table[Ceiling vol]),Table[Actual units]<>0)

Measure = divide([A_units],[C_Vol])

Format Measure as a %.

[CNENFRNL · Accepted solution]

Simple enough

Actual Unit Utilization =
CALCULATE(
    SUM( Table1[Actual Units] ) / SUM( Table1[Ceiling Vol] ) * 100,
    Table1[Actual Units] <> 0
)

[AlB]

Hi @Anonymous 

Try this

Measure_ =
VAR auxT_ = FILTER ( Table1, Table1[Actual Units] > 0 )
RETURN
    100 * DIVIDE ( SUMX ( auxT_, [Actual Units] ), SUMX ( auxT_, Table1[Ceiling Vol] ) )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.