Now do it in Power BI Desktop
Ranking, text and formatting: Power BI Desktop exercise
Rank, label and format, the finishing work on every report. 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. Rank the countries
Create Country Rank by Total Sales and show it by Country.
2. Top 3 countries together
The combined sales of the three best-selling countries.
3. Product codes
Add a calculated column with the first three letters of Product in capitals, like CAR. Then count the distinct codes.
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. Rank the countries
Country Rank = RANKX(ALL(financials[Country]), CALCULATE(SUM(financials[Sales])))| Country | Country Rank |
|---|---|
| Canada | 2 |
| France | 3 |
| Germany | 4 |
| Mexico | 5 |
| United States of America | 1 |
2. Top 3 countries together
Top 3 Sales = CALCULATE(SUM(financials[Sales]), TOPN(3, ALL(financials[Country]), CALCULATE(SUM(financials[Sales]))))Result: 74,271,657.33
3. Product codes
Codes = DISTINCTCOUNT(financials[Product])- Modeling → New column: Product Code = UPPER(LEFT(financials[Product], 3))
Result: 6