PowerBI Total Cost/Unit sums monthly calculations rather than creating a total for selected months

user_13682811 0
Viewed 166

In my dashboard I'm adding a cost/unit metric. The problem is, if I select more than one month, it just adds the monthly calculations. For example,

Store Month Units Cost Cost/Unit
Blue Jan 10 20 2
Blue Feb 20 60 3
Red Jan 10 30 3
Red Feb 20 80 4

When I create a table with with these, and select January and February in the Splicer it shows something like this:

Store Cost/Unit
Blue 5
Red 7

Of course this isn't the real total cost/unit for both months. Instead I'd like for it to show:

Store Cost/Unit
Blue 2.67
Red 3.67

I am pretty new to PowerBI so I'm sorry for the lack of words on how to explain this, but help would be very much appreciated!

powerbi-desktop powerbi dashboard finance
1 Answers

Going by the result, it seems that you might have created your metric as a calculated column rather than a measure. This would mean that the expression is evaluated and stored for each row and it would sum up for aggregated values (like any existing column).

If you create the same metric as a measure:

UnitCost = Divide (SUM('TableName'[Cost]), SUM('TableName'[Units]))

Power BI would calculate it based on the context and you would always have the correct value.

user_182157350
Related