Log In
6

Now do it in Power BI Desktop

CALCULATE and filter context: Power BI Desktop exercise

Change the filter context with CALCULATE, and check that totals behave the way you expect. You practised this in the browser; now do it in your own copy of Power BI Desktop (free from Microsoft), on the same data, and check your numbers below.

1. Get the data

In Power BI Desktop: Home → Get data → Text/CSV, pick each file, then Load. Check that numbers came in as numbers and dates as dates (Table view, column header icons).

2. The tasks

  1. 1. One country, every row

    Create Canada Sales: sales for Canada, whatever the visual is filtered to.

  2. 2. Share of all countries

    Create % of Total: each country's share of all countries' sales. Show it by Country.

  3. 3. Big deals

    Sales from rows where Sales is over 100,000.

3. Check your numbers

Try every task first. Then open each answer: the numbers below come from the same data, so yours should match exactly.

1. One country, every row
Canada Sales = CALCULATE(SUM(financials[Sales]), financials[Country] = "Canada")

Result: 24,887,654.88

2. Share of all countries
% of Total = DIVIDE(SUM(financials[Sales]), CALCULATE(SUM(financials[Sales]), ALL(financials[Country])))
Country% of Total
Canada20.96%
France20.51%
Germany19.80%
Mexico17.65%
United States of America21.08%
Total100.00%
3. Big deals
Big Deal Sales = CALCULATE(SUM(financials[Sales]), financials[Sales] > 100000)

Result: 107,731,876.38

The lessons behind this exercise