Archive

Posts Tagged ‘Tabular’

SSAS – Tabular Calculated Measures

July 10, 2012 1 comment

It is always fun to have something in SSAS Cube for drag and drop at reporting. Here planning to do some calculated measures in SSAS Tabular mode and intern to use in Power View reports.

Calculated Measures:

1. Ranks to Internet Selling Countries

 
InternetSales Country Rank:=IF(ISBLANK(SUM('Internet Sales'[Sales Amount])), BLANK(),  RANKX(ALL('Geography'[Country Region Name]), SUMX(RELATEDTABLE('Internet Sales'), [Sales Amount])))

2. Ranks to Internet Selling Cities

 
InternetSales Cities Rank:=IF(ISBLANK(SUM('Internet Sales'[Sales Amount])), BLANK(),RANKX(ALL('Geography'[State Province Name]), SUMX(RELATEDTABLE('Internet Sales'), [Sales Amount])))

3. Internet Sales MTD

 
SalesMTD:=TOTALMTD(SUM('Internet Sales'[Sales Amount]),'Date'[Date])

4. Internet Sales QTD

 
SalesQTD:=TOTALQTD(SUM('Internet Sales'[Sales Amount]),'Date'[Date])

5. Internet Sales YTD

 
SalesYTD:=TOTALYTD(SUM('Internet Sales'[Sales Amount]),'Date'[Date])

We could see the Tabular model as



Power View Report:

We could develop very useful reports just by drag and drop of these calculated measures and applying filter conditions as



Points to consider:

1. Discussed only basic Tabular calculated measures
2. Used these measures to use as filter to get the TOP results
3. Running totals in one of the common scenarios in any domain reporting

Categories: SSAS Tags: