Dividing totals of 2 columns as they are filtered ?

[kriman]

I have a table visual with 2 columns (A, B), and the table is filtered by dates and by sales associate.

Each column shows a TOTAL amount at the bottom of the table.

I would like to produce a 3rd table column that divides the TOTALS shown by the table e.g. (B/A).

The 3rd column value should change as I use the filters with different dates and different sales associates.

Thanks

[mahoneypat]

This should work (replacing Table and column with your actual names.

NewMeasure = DIVIDE(SUM(Table[Column1]), SUM(Table[Column2]))

Regards,

Pat

[kriman]

Thanks. Sorry but it didn't work.

The 5th column to the right has to be the total (153.8 divided by 142.7), so the 5th column total should be 107.83.

I can't group this because the 5th total should be based on random filtering by associate # and by dates range.

[mahoneypat]

Can you explain more about the direct_hours and Earn columns?  Are those columns or measures?  Do you have a single table in your model?

Regards,

Pat

[kriman]

Thanks.

They are forecasted hours Vs actual hours of work, displayed in the table and filtered ramdomly by date range & associate ID #.

Yes, data coming from single table.

[mahoneypat]

Is there an aggregation on the direct_hours and earn columns? or are those the row values?  If so, what aggregation?

Regards,

Pat

[kriman]

Aggregations, yes.

A single day, might have several rows. So the displayed table shows a sum of each day.

[mahoneypat]

You didn't say which aggregation so I assume it is a SUM.  Try this expression instead

NewMeasure =
SUMX (
    SUMMARIZE (
        Table,
        Table[assoc_id],
        Table[activity_date]
    ),
    CALCULATE (
        DIVIDE (
            SUM ( Table[EARN] ),
            SUM ( Table[direct_hours] )
        )
    )
)

Regards,

Pat

[kriman]

Hi,

Sorry, no luck. 

Result is 52.2, but it should be 107.83.

________________________________________________

Perfor% =

SUMX (

SUMMARIZE (tblWorkHistory,

tblWorkHistory[assoc_id],

tblWorkHistory[activity_date]

),

CALCULATE (

DIVIDE (

SUM ( tblWorkHistory[EARN]), SUM(tblWorkHistory[direct_hours])

)))

[v-jingzhang]

Hi @kriman 

I test this with two measures as below and both of them work well. 

Measure = SUM('Table'[EARN])/SUM('Table'[direct_hours])

Measure 2 = DIVIDE(SUM('Table'[EARN]),SUM('Table'[direct_hours]))

I noticed that the total value 68.77 in your screenshot is nearly the sum total of the values above it, and the Perfor % value in every row is not calculated correctly, so I guess if there is any typo in your measure? Can you please check it and try again?

Best Regards,

Community Support Team _ Jing Zhang

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