Calculating Unit Rate

lexusap

New Member
Joined
Dec 21, 2014
Messages
19
Hi,

A newbie in Power BI sphere and an excel vba ninja here. Currently I am tasking myself to actually move all the report from excel and converted to power pivot. One of the report that i need to create is the Proft and lost, which comprise of 2 section in each month, Volume or $ and second one which is the unit rate.

The current schema, the Volume and Financial appear in a same column, see column E, and the calculation on unit rate is simply the $ divide by volume. to complicate things, there are multiple product involve and sometimes the divisor volume can be different, resulting in different unit rate.

I have attached sample picture with a small mock data, where if you use selection on cell C4 drop down and select between Volume 1 to Volume 6, the unit rate calculation on column F will change.

How can i achieve this unit rate calculation in power pivot ? Do i need to create some sort of hierarchy and link the relationship ?

Thanks in Advance.
AM
 

Attachments

  • Col5.png
    Col5.png
    34.7 KB · Views: 1
  • Volume3.png
    Volume3.png
    27.3 KB · Views: 1
  • Volume1.png
    Volume1.png
    100.4 KB · Views: 1

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,136,773
Messages
5,677,642
Members
419,708
Latest member
PhilD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top