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
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
