How to Multiply a Measures With Certain Factor in different level of drill.

Question

Hi Gurus,

I have one requirement where I have to multiply one of my measure columns with certain factor. The factor value will differ at different level of hierarchy. For example I have time hierarchy as Year -> Quarter -> Month now when I am at Year level it should multiply with factor X(Yearly Factor), when I drill down to Quarter my measure should get multiplied with factor Y (Quarterly Factor) and when I am at drill level Monthly it should get multiplied with Factor Z (Monthly Factor).

Assume all the factor value is calculated and stored in my database.

How to approach for this problem solutions's? I need your expert advertise in this.

Kindly Let me know if more information is required or if problem statement is not clear.

Thanks & Regards,

Vivek Kumar

Thomas Dodds · Answer

set up a second fact table that 'aggregates' the rate at the appropriate level ... then level-based the factors ...

user13674350 · Answer

Hi Thomas,

Thanks for your reply. I have only one fact and there are 2-3 measures in it. And there is one time dimension associated with that fact.

Now when I pull Year Number from time dimension and one Measure from fact, what result I am getting is absolutely correct and drill also works perfectly. Drill goes to quarter Number then drill to month.

Now extra thing I want here is I have to multiply the measure by some factor and these factor are calculated on basis of days, so if it is year factor is different when it is quarter factor is different and for month factor is different. I have calculated this factor separately and it is working fine. But how should I multiply this with measures at different level of hierarchy.

Let me know if you can connect with me on skype or call.

Thanks & Regards,

Vivek Kumar

Thomas Dodds · Answer

Looks you've got a fact at various time levels, you just need to store and model your factors at the same levels ... then you can combine the fact column and the factor at the appropriate level.  How do you have the physical model now?  We'll start there...