Power pivot formula not calculating rate correctly.

[Myexcel7]

I have two columns-volume and total $. Calculated field is greyed out in Power Pivot. When I try to divide total $ column by volume column to display rate, it is not only giving me wrong rate but also rates are adding up in the total row. Any help in how to calculate rate so that total row does not show addition of rates. In normal Pivot table, it is simply calculated field total $/total volume which give me rates and also total row gives me correct effective rates. Hope this makes sense.

		VOL	Total $	Rate
City A	AA	10​	5​	$ 0.50
	BB	20​	7​	$ 0.35
	CC	20​	7​	$ 0.35
Total		30​	12​	$ 1.20
City B	AA	40​	5​	$ 0.13
	BB	50​	7​	$ 0.14
	CC	27​	12​	$ 0.27
Total		117​	24​	$ 0.53

 

[Matt Allington]

This is the SUM vs SUMX problem “totals don’t add up”. You can read about it here

When to Use SUMX() vs SUM() in DAX

Learn the main insights of SUMX() vs SUM() in DAX with examples, so you can decide which formula works best to get excellent reports.?

exceleratorbi.com.au

You really should have a star schema, too.

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

 

[Myexcel7]

Thank you for your help!