Home > SSAS > SSAS – Tabular Calculated Measures

SSAS – Tabular Calculated Measures

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:
  1. February 19, 2013 at 5:33 am

    This is the third article, of your blog I personally went through.
    But yet I personally love this one, “SSAS – Tabular Calculated Measures
    BI passion” the very best. Cya ,Eugenia

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: