Recently, I came across a requirement where the user selects the month number in the slicer, and based on that, the sales values in the table visual will be compared with sales values of other months using conditional formatting.
Here, I’ll provide a step-by-step implementation of the same using the INDEX DAX function in Power BI,
Before we jump into the implementation, let’s take a closer look at the INDEX function.
The INDEX function retrieves a row from a table based on a specific position indicated by the position parameter. It works within a defined partition and considers the specified order for sorting.
Syntax:
INDEX(<position>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>][, <matchBy>])
position – The absolute position (1-based) from which to obtain the data:
Position is positive: 1 is the first row, 2 is the second row, etc.
Position is negative: -1 is the last row, -2 is the second last row, etc.
relation – (Optional) A table expression from which the output is returned.
orderBy – (Optional) An ORDERBY() clause containing the expressions that define how each partition is sorted.
blanks – (Optional) An enumeration that defines how to handle blank values when sorting. Currently, the only supported value is DEFAULT.
partitionBy – Optional) A PARTITIONBY() clause containing the columns that define how <relation> is partitioned.
matchBy – (Optional) A MATCHBY() clause containing the columns that define how to match data and identify the current row.
I hope the above explanation provided you with some idea about the INDEX function. If you want to delve deeper into this function, I would recommend referring to the official Microsoft documentation.
Let’s begin the implementation of comparing selected month sales vs. other months using the INDEX function in Power BI.
Requirement:
When the user selects the month number in the slicer, the sales values of the current month will be compared with the selected month in the table visual using up and down arrows to indicate the increase and decrease in sales.
Implemetation:
Data Modelling
Create Necessary Tables and Columns
Dim Date – We need to create a proper date table with all the required columns based on our specific requirements. This date table will be connected to our fact table. Here, I have defined the date table using the CALENDAR function, but we can also create it using the Power Query Editor or obtain a pre-defined date table from any source that meets our requirements. I intend to initiate the date table from January 1st, 2013.
Dim Date = CALENDAR(
DATE(2013,01,01),TODAY())
Month Number Parameter – Under the Modeling tab, select “New Parameter” (Numeric Range) option and fill in the required information as shown in the image below. Then, click on “Create” to create the parameter. We will use this parameter value in the slicer to allow the user to select the month.
Tables used:
Financials – This table contains sales and profit information.
Dim Date – This table holds the date and other columns required for the implementation.
Month Number (Parameter) – This parameter table contains the month numbers from 1 to 12 to be used in the slicer.
We have created one-to-many relation between Dim Date and financials table with ‘Dim Date'[Date] and ‘financials'[ Date] as a relationship key between the tables.
DAX Measures:
Sales Measure
Sales Sales Measure =
// Define a variable to store the selected month number from the 'Month Number' table.
VAR _selectedMonth = SELECTEDVALUE('Month Number'[Month Number])
// Define a variable to store the selected month name from the 'Dim Date' table.
VAR _selectedMonthName = SELECTEDVALUE('Dim Date'[MonthName])
// Calculate the sales for the current month using the 'financials' table and 'INDEX' function.
VAR _currentMonthSales =
CALCULATE(
SUM(financials[ Sales]),
INDEX(
_selectedMonth,
ALL('Dim Date'[Month Number], 'Dim Date'[Year]),
ORDERBY('Dim Date'[Month Number], ASC),
DEFAULT,
PARTITIONBY('Dim Date'[Year])
),
ALL('Dim Date')
)
// Calculate the total sales for the selected month using the 'financials' table.
VAR _monthSales = SUM(financials[ Sales])
// Check if the current month is the same as the selected month.
// If it is, display the message with the selected month name and its sales value.
// Otherwise, display the total sales for the selected month.
VAR _result =
IF(
SELECTEDVALUE('Dim Date'[Month Number]) = _selectedMonth,
"Selected Current Month is " & _selectedMonthName & " (" & _currentMonthSales & ")",
_monthSales
)
// Return the result based on the conditions above.
RETURN _result
Selected Month Conditional Formatting
This measure is used for conditional (Increase/Decrease) formatting in Power BI table visual,
Selected Month Conditional Formatting =
// Define a variable to store the selected month number from the 'Month Number' table.
VAR _selectedMonth = SELECTEDVALUE('Month Number'[Month Number])
// Calculate the sales for the selected month using the 'financials' table and 'INDEX' function.
VAR _monthSales =
CALCULATE(
SUM(financials[Sales]),
INDEX(
_selectedMonth,
ALL('Dim Date'[Month Number], 'Dim Date'[Year]),
ORDERBY('Dim Date'[Month Number], ASC),
DEFAULT,
PARTITIONBY('Dim Date'[Year])
),
ALL('Dim Date')
)
// Define a variable to store the result of the conditional formatting.
// If the current month is the same as the selected month, display "Selected Current Month."
// Otherwise, check if the sales for the selected month are greater than the calculated '_monthSales'.
// If yes, return 1 (for formatting an increase indicator), else return 0 (for formatting a decrease indicator).
VAR _result =
IF(
SELECTEDVALUE('Dim Date'[Month Number]) = _selectedMonth,
"Selected Current Month",
IF(
SUM(financials[Sales]) > _monthSales,
1,
0
)
)
// Return the result for conditional formatting.
RETURN _result
Selected Month Name (Subtitle)
This measure is used in the subtitle to provide meaningful information to the user based on the slicer selection,
Selected Month Name (Subtitle) =
// Define a variable to store the selected month number from the 'Month Number' table.
VAR _selectedMonthNumber = SELECTEDVALUE('Month Number'[Month Number])
// Calculate the month name for the selected month using the 'Dim Date' table and 'CALCULATE' function.
VAR _monthName =
CALCULATE(
SELECTEDVALUE('Dim Date'[MonthName]),
'Dim Date'[Month Number] = _selectedMonthNumber
)
// Define a variable to store the formatted subtitle with the selected month name.
// The subtitle will indicate a comparison of the selected month with other months.
VAR _selectedMonthName = "Comparison of " & _monthName & " with other months"
// Return the formatted subtitle.
RETURN _selectedMonthName
Reporting
We will be using the slicer and table visual in Power BI. Please follow the steps below for the implementation:
Step 1:
First, we need to create a slicer to select the month number. To do that, we will use the “Month Number” column from the parameter table in the slicer. I have randomly selected month number 5.
Note: I have customized the title background and values background based on my preference. Feel free to format the visual according to your own preferences as well.
Step 2:
Drag and drop the “MonthName” column from the “Dim Date” table onto the table visual canvas. Next, drag and drop the “Sales” measure into the table visual. Your visual would look like this,
Step 3:
To apply conditional formatting, you need to use the“Selected Month Conditional Formatting” measure in the conditional formatting option.
To access the conditional formatting option, right-click on the “Sales” measure in the “Values” field pane of the “Sales” table, and then choose “Conditional formatting” followed by “Icons.”
Step 4:
In the conditional formatting pane, select the following highlighted values from the dropdowns: for the value 1, an up-arrow with green color to represent higher sales, and for the value 0, a down-arrow with red color to indicate lesser sales. Please ensure that the plotting is done carefully to achieve the desired visual representation.
Step 5: Title and Subtitle
To create the title, you can directly input the text values in the format pane of the text title placeholder. However, for the subtitle, you need to select conditional formatting in the subtitle option to make the subtitle text work dynamically using the “Selected Month Name (Subtitle)” measure.
Title:
Subtitle:
Select the “fx” (function) icon here, and choose the necessary fields in the conditional formatting pane as mentioned, similar to the examples shown in the images below.
Result:
Note: I have customized the title background and values background based on my preference. Feel free to format the visual according to your own preferences as well.
Please refer the attached file and let me know your comments.
Comments
-
BunzietheBoo
on:
🏆 Power BI DataViz World Championships | Week 4 F...
-
thefarside
on:
🏆 Power BI DataViz World Championships | Week 4 W...
- Magudeswaran_MR on: How to Calculate Cumulative Sum in Power BI
-
Dera__Igboejesi
on:
🏆 Power BI DataViz World Championships | Week 3 F...
- M_S_MANOHAR on: Dynamic Subscriptions
-
LucasKoch2900
on:
🏆 Power BI DataViz World Championships | Week 3 W...
-
slindsay
on:
🏆 Power BI DataViz World Championships | Week 4 |...
-
JamesDBartlett3
on:
PBIP fx hack: dynamic expressions for properties -...
- TBH on: How to add Gradient Text in Power BI dashboard
-
Jasmine_319
on:
🏆 Power BI DataViz World Championships | Week 2 F...