DAX to divide table totals from 2 columns

[Anonymous]

Hi,

In the below table (direct hours (A), and Earned (B)) are delivering the right SUM total.

However, the (performance (C)) column needs to be 153.88 divided by 142.7 .

Basically, I need a table that Sums column A, Sums column B, but column C has to divide the totals of B & A (B:A).

Moreover, the table is being filtered by users, using filters for (Assoc_ID, and WK_end_date).

I tried this, but it doesn't work:

CALCULATE(DIVIDE(SUM(tblTempReport[earned]),(SUM(tblTempReport[direct_hours])))

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Try this.

Measure = 
VAR x = 
MAX([Earned])/ MAX([Direct hours])
RETURN
IF(
    HASONEFILTER(Sheet1[Direct hours]),
    x,
    DIVIDE( SUM(Sheet1[Earned]), SUM(Sheet1[Direct hours]) )
)

Best regards,
Lionel Chen

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

[amitchandak]

@Anonymous , This formula seems fine. What is the issue you are getting

CALCULATE(DIVIDE(SUM(tblTempReport[earned]),(SUM(tblTempReport[direct_hours])))

Can you share sample data and sample output in table format?

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Anonymous]

Thanks,

Yes, it seems that the above formula is OK, but the above result for C should be 107.83, not 14.78.

Here is a more clear example: