Multiple tier rollup

[rollo]

In the example below, I have created a calculated column "Profit Per Unit" using the following expression:

Profit Per Unit = Divide('Titan_DMS qryNVunitsandGP'[Profit],('Titan_DMS qryNVunitsandGP'[Units]),0)

and this works perfectly at the table view level.

Division Franchise    Model                 Units           Profit   Profit Per Unit

A          Landrover     Range Rover        10             30000      3000

A          Jaguar           F - Type                 6             24000      4000 

B          Landrover     Discovery             12             30000      2500

B          Nissan          Pulsar                     6             24000      4000 

C          Nissan          Pulsar                     8             12000      1500

My problem arises when I try and create a line chart report and want to slice with filters for:

Division

Franchise

Model

and the result I am getting for these summaries is not correct.

It would appear that it is adding all the Per Unit figures together instead of having weighted averages.

I have only been using Power BI for a few days so please bear with me.

Hope you can help

Rollo

[v-chuncz-msft · Accepted solution]

@rollo,

You need to add a measure instead.

http://community.powerbi.com/t5/Desktop/Combined-graph-doesnt-calculated-graph-line/m-p/359331#M1620...

[rollo]

Hi v-chunz

This does not work for graphs.

It works fine in the table format but when you used the new measure "Profit per unit", it adds up all the individual Profit Per Unit calculations and displays the aggregate total on the line chart which is completely wrong.

[rollo]

Thanks v-chunz

This now works perfectly.

I hadn't appreciated the difference between adding a measure and adding a column.

Cheers