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: