# Combine facts and dimensions to create new calculated member

• ### Question

• Hi all,

I have a complex case as follow. The first image is data source and I need to create something like new fact on Cube which support two case:

Case 1: only CY_Year on grid. Data will show as Fact_AllMedia(see below image)

Case 2: when we have CY_Year and CY_Year_Quarter, data will show as Fact_Detail(see below image)

I know it look complex but it is a real requirement. Please show me any way to do this.

Thanks all.

• Edited by Thursday, March 6, 2014 2:03 AM
Wednesday, March 5, 2014 10:37 AM

• Assuming that I understand, you could define a calculated member like:

SCOPE(Measures.NewMeasure);

this=measures.fact_detail;

end scope;

SCOPE(Measures.NewMeasure,dimMedia.Media.[All Media]);

this=Measures.fact_allmedia;

end scope;

Hope this helps,

Ken

• Proposed as answer by Friday, March 14, 2014 2:30 PM
• Marked as answer by Tuesday, March 18, 2014 1:52 AM
Thursday, March 6, 2014 8:24 PM

### All replies

• Hi khoana,

TV 50, Radio 30, but All media 100. Where another 20 ?

Wednesday, March 5, 2014 12:00 PM
• you need to store it as below in cube as i understand

FactTable
----------

Date_Key,Media_Key,Detail

DateTable
--------------
Date_key,Date,Year,Quarter etc

MediaTable
---------------

The data should be stored at Media grain level and you dont need to include the All Media data in it ie total row. The cube will still be able to aggregate them to All Media level once your set aggregatable property to true in SSAS.

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Wednesday, March 5, 2014 12:10 PM
• Hi Khoana,

The data source suggests  that the fact table already contains aggregated facts  “Fact_Allmedia” and aggregated lines on aggregated members “all media”.   One would think this is redundant as the cube would normally take care of those aggregations.

If possible I would suggest not to take those aggregations over in the ETL and dsv,.

Speculating you have good reasons to maintain them, I think the “Fact_Allmedia” could easely be ignored as you can define an “all member”  on the media set.  To make it works  you should also define a (supplemental ?)  media dimension where “All media” as found in your data source , should not be a member of.

Philip,

Wednesday, March 5, 2014 12:21 PM
• Hi all,

Thanks for your suggestion. In my example, the KEY point is "All Media" is not the total of media details. So, I think we can split into 2 facts and use some calculation to show data as expected. But I am really get stuck in this.

Hi Philip,

I really not fully understand your advice. would you please show me more detail.

Thanks all

Thursday, March 6, 2014 2:14 AM
• Assuming that I understand, you could define a calculated member like:

SCOPE(Measures.NewMeasure);

this=measures.fact_detail;

end scope;

SCOPE(Measures.NewMeasure,dimMedia.Media.[All Media]);

this=Measures.fact_allmedia;

end scope;

Hope this helps,

Ken

• Proposed as answer by Friday, March 14, 2014 2:30 PM
• Marked as answer by Tuesday, March 18, 2014 1:52 AM
Thursday, March 6, 2014 8:24 PM