DAX measure to iterate each row for correct division (for total as well)

Question

I am not sure if there's a way in DAX to create a measure that would help me with the following:

• Calculate the efficiency by day
• Display the total efficiency in a pivot table / PowerBI matrix as the overall total and not as sum of the daily efficiency

Here's a simple example:

Where:

• Total Categories = Category1 + Category2 + Category3
• Efficiency = (Total Categories + Category4*0.33)/Category4

At first I've created measures for each category (e.g. TotalCateg1 = SUM[Category1] etc.) and hopping to get the right result in the end. My problem is I am not able to get both the daily efficiency and the total right. Is there a way around it?

Answer 1

For Total Categories use this formula:

=SUM([Category 1])+SUM([Category 2])+SUM([Category 3]) .

then for the Efficiency use this formula:

=([Total Categories]+SUM([Category 4])*0.33)/SUM([Category 4])