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

EVALUATE
( SUMMARIZE
( FILTER
( financials,
AND
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?