SUM of a measure

[Anonymous]

Hi!

I have a problem considering calculating the sum of a measure. I have seen the problem in other posts but haven't been able to make the suggested solutions work in my report.

• Car-ID (name of the car)
• Route-ID (name of the route the car drives)
• Driving Cost (driving cost per route)
• Total Cost (driving cost per route multiplied by the number of times each car takes the particular route

In the visualization above the values are shown for one Car-ID and several routes which also occur several times, hence the multiplication to calculate Total cost.

Driving cost and Total driving cost are measures whereas the Car-ID and Route-ID are found in two separate tables and related to each other.

I need the total that is highlighted to show the sum of the rows above (32100) as is done in the Driving Cost-column. For the rows everything works correctly.

In DAX i have the following:

Driving Cost =SUM('Routes'[Cost per route])

 Total Cost = [Driving Cost]*count('Routes'[Route-ID])

Appreciate all help!

[Arul]

Hi @Anonymous 

For calculating total cost you need to create new calculated column and it automatically summarize the total value as a sub-total. You don't need create any measure for this to sum.

FORMULA FOR CREATING NEW CALCULATED COLUMN:

NEW CALCULATED COLUMN=

[Driving Cost]*number of times each car takes the particular route

Here you don't need to count the route id, you need to multiply it by number of times that the car takes on the particular route.

 Hope you get my point.

[Anonymous]

Hi @Arul !

Thanks for your tip. I've tried creating a calculated column in my table containg information about the cars. However, I still get the wrong sum for the cars and they are still calculated as in my first post.

This was the formula I entered for the calculated column:

Total Cost = [Driving Cost] * COUNT('Cars and Routes'[Route-ID])

The Cars nad Routes table contains one clumn for the car and another column for the routes where one route can exist several times per car as well as for other cars, hence the count in the formula above.

Below is my measure for the driving cost. The calculations for this one is correct as they are shown in the table in my first post but perhaps it affects the calculation for the total cost.

Driving Cost = CALCULATE(SUM('Routes'[Cost per Route]);CROSSFILTER('Cars and Routes'[Route-ID];'Routes'[Route-ID];both))

Thanks!

[Arul]

Hi @Anonymous 

Can you share your pbix file, because it will helpful for me to sort out your problem.

Thanks & regards,

-Arul