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

1
votes

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: enter image description here

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?

1
excelpowerbipowerpivotdaxmeasure

1 Answers

1
votes

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])

enter image description here