Data Warehousing 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]) /
( SUM(financials[ Sales]),
  ALLEXCEPT(financials, financials[Year], financials[Country])
) * 100

    ( financials,
      ( 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]


  • 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?


  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: Logo

You are commenting using your 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

%d bloggers like this: