I’m going to get such a reputation, I love KPI’s but I’ve seen them used badly too many times. A bad KPI is TOXIC, so make sure you know what your KPI’s are measuring.
In today’s video we went through how you set up a KPI within Power BI Desktop and use it. So all of a sudden we’re starting to write DAX. I’m going to keep trying to help you avoid some of the pitfalls, and I hope you’re enjoying this as much as I am.
Download the dataset: https://www.kaggle.com/xtyscut/video-games-sales-as-at-22-dec-2016csv
DAX Statements
AVG Critic Score =
AVERAGE ( ‘Video Games'[Critic_Score] )
AVG Critic Score ALL =
CALCULATE ( AVERAGE ( ‘Video Games'[Critic_Score] ), ALL ( ‘Video Games’ ) )
AVG User Score =
AVERAGE ( ‘Video Games'[User_Score] ) * 10″
AVG User Score ALL =
CALCULATE ( AVERAGE ( ‘Video Games'[User_Score] ), ALL ( ‘Video Games’ ) ) * 10
KPI Critic =
VAR allValue = [AVG Critic Score ALL]
VAR Test = [AVG Critic Score]
RETURN
IF (
allValue > ( Test + [Tolerance] ),
-1,
IF ( allValue < ( Test – [Tolerance] ), 1, 0 )
)
KPI User =
VAR allValue = [AVG User Score ALL]
VAR Test = [AVG User Score]
RETURN
IF (
allValue > ( Test + [Tolerance] ),
-1,
IF ( allValue < ( Test – [Tolerance] ), 1, 0 )
)
Tolerance =
1.00
BASE =
SUM ( Sales[Sales (M)] ) * 1000000
EU Sales =
CALCULATE ( [BASE], Sales[Location] = “EU_Sales” )
Global Sales =
CALCULATE ( [BASE], Sales[Location] = “Global_Sales” )
JP Sales =
CALCULATE ( [BASE], Sales[Location] = “JP_Sales” )
NA Sales =
CALCULATE ( [BASE], Sales[Location] = “NA_Sales” )
Other Sales =
CALCULATE ( [BASE], Sales[Location] = “Other_Sales” )
SalesSelection =
SWITCH (
[SwitchSelection],
“Global_Sales”, [Global Sales],
“EU_Sales”, [EU Sales],
“NA_Sales”, [NA Sales],
“JP_Sales”, [JP Sales],
“Other_Sales”, [Other Sales],
[Global Sales]
)
SwitchSelection =
SELECTEDVALUE ( Sales[Location], “Global_Sales” )
Test GLobal Sales =
CALCULATE (
SUM ( Sales[Sales (M)] ) * 1000000,
FILTER ( Sales, Sales[Location] = “Global_Sales” )
)
Games =
COUNTROWS ( ‘Video Games’ )