Log In
5

Now do it in Power BI Desktop

DAX basics: Power BI Desktop exercise

Write the core measures in your own model and put them in one table. 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. Profit margin

    Create Total Sales, Total Profit and Profit Margin (profit divided by sales, safely). Show the margin by Segment.

  2. 2. Average units per order line

    How many units does an average row sell?

  3. 3. Distinct products

    How many different products does each country sell?

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. Profit margin
Profit Margin = DIVIDE(SUM(financials[Profit]), SUM(financials[Sales]))
SegmentProfit Margin
Channel Partners73.13%
Enterprise-3.13%
Government21.69%
Midmarket27.71%
Small Business9.77%
Total14.23%
2. Average units per order line
Avg Units = AVERAGE(financials[Units Sold])

Result: 1,608.29

3. Distinct products
Products Sold = DISTINCTCOUNT(financials[Product])
CountryProducts Sold
Canada6
France6
Germany6
Mexico6
United States of America6
Total6

The lessons behind this exercise