Data Warehousing, BI and Data Science

4 November 2018

Percentage of Total and Filter on 2 Columns

Filed under: Data Warehousing — Vincent Rainardi @ 8:02 pm

This query shows how to Filter on 2 columns, add 2 measures using AddColumns, and create a measures to calculate the percentage of total for a certain year and country.


DEFINE MEASURE financials[Sales%] =
SUM(financials[ Sales]) /
CALCULATE
( SUM(financials[ Sales]),
  ALLEXCEPT(financials, financials[Year], financials[Country])
) * 100

EVALUATE
ADDCOLUMNS
( SUMMARIZE
  ( FILTER
    ( financials,
      AND
      ( financials[Country] = "Canada",
        financials[Year] = 2014
      )
    ),
    financials[Month Name],
    financials[Month Number]
  ),
  "Sales", FORMAT(CALCULATE(SUM(financials[ Sales])), "#,###"),
  "Sales%", FORMAT(financials[Sales%],"##.##")
)
ORDER BY financials[Month Number]

Explanation:

  • The Sales% is defined as Sales divided by the sum of Sales for the year and country currently selected.
  • The financial table is filtered on Country = Canada and Year = 2014.
  • Then two columns are chosen from this table: Month Name and Month Number.
  • Then 2 measures got added: Sales and Sales% (which was defined above)
  • Finally it’s ordered on Month Number.

Note: we can’t filter on 3 columns using And function. The maximum argument on And function is two. The And operator (&&) can take more than 2 arguments.

PS.Any one know how to hide the Month Number column?

Advertisements

2 Comments »

  1. May be SELECTCOLUMNS will help

    Comment by Maxim Zelensky — 4 November 2018 @ 8:39 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: