This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	PowerPivot (72 exercises)
Topic ==>	KPIs (5 exercises)
Level ==>	Average difficulty
Subject ==>	Power BI training

Create an absolute KPI that a product should contribute 10% of total sales

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file.

If you haven't already done so, run the SQL script in the above folder (copying and commercial use prohibited) to generate a database called MAM.

Create a new workbook, and within this a data model containing just two tables:

Just import the tblProduct and tblTransaction tables.

Now create the following calculated fields (the formulae are given below, if this is too late in the course!):

Field	Notes
ThisProduct	The total values of sales for the current query context (use SUMX to sum price * quantity).
AllProducts	The total values of sales for the current query context, but for all products (use CALCULATE and SUMX together, with a filter across all products using ALL).
Proportion	The first calculated field divided by the second.

Here are the formulae, if your head is too buzzy by this stage of the course:

Field	Notes
ThisProduct	*=SUMX( 'Transaction', [Price][Quantity] )**
AllProducts	*=CALCULATE( sumx( 'Transaction', [Price][Quantity] ), ALL('Product'))**
Proportion	=[ThisProduct] / [AllProducts]