Imagine this - every report you create has that one show-stopping feature, a "Wow" factor that captivates your audience and elevates your data analysis game. In the world of Power BI, one such feature that can add an extra layer of dynamism and insight is the art of crafting dynamic Top N calculations. Let's explore how you can seamlessly include this "Wow" feature in almost every report you create.
Introduction
Have you ever wanted to create reports that dynamically showcase the top N items based on user preferences? That's the power of dynamic Top N calculations in Power BI. In this guide, we'll delve into the process of creating dynamic reports that allow users to choose how many, by what category, and how to rank the data.
In Top N we can give 3 choices to report users.
How many. Like 5, 10, etc.
By What. Like Supplier, Product, etc.
How. Like Quantity wise, Discount wise , etc.
Lets see each one and at the end we will see how to combine all. I have taken sample data
to explain. Can also download pbix using github Link. Feel free to change formulas according to your data.
Parameters
To kick things off, we need to set up parameters. Think of these as the controls that empower your users to tailor the report according to their needs.
A. Create a range parameter, adding it to a slicer for user interaction.
Parameter = GENERATESERIES(1, 1000, 1)B. Create Field Parameters for "By What" and "How"
These parameters define the dimensions and measures users can select for their Top N analysis.
Parameter 2 = {
("Department", NAMEOF('sample'[Department]), 0),
("Product", NAMEOF('sample'[Product]), 1),
("Supplier", NAMEOF('sample'[Supplier]), 2),
("Expense_Type", NAMEOF('sample'[Expense_Type]), 3)
}Parameter 3 = {
("Discount", NAMEOF('sample'[Discount]), 0),
("Quantity", NAMEOF('sample'[Quantity]), 1),
("Total Cost", NAMEOF('sample'[Total Cost]), 2),
("Unit Cost", NAMEOF('sample'[Unit Cost]), 3)
}Measures
Now, let's create measures that will serve as visual level filters for the Top N functionality.
How Many. This formula helps to dynamically adjusting the number N from Top.
TopN Supplier by quantity =
VAR SelectedTop = SELECTEDVALUE ( 'Parameter'[Parameter] )
RETURN
SWITCH (
TRUE (),
SelectedTop = 0, 1,
CALCULATE (
RANKX ( ALL ( 'sample'[Supplier] ), [Quantity] ,, DESC ),
ALLSELECTED ( 'sample'[Region] ),
ALLSELECTED ( 'sample'[Approval_Status] ),
ALLSELECTED ( 'sample'[Department] )
) <= SelectedTop, 1, BLANK()
)2. How Many and By What: This measure allows you to explore the Top N items based on dynamic dimensions.
TopN Dimension wise =
VAR SelectedTop = SELECTEDVALUE('Parameter'[Parameter])
VAR SelectedCategory = SELECTEDVALUE('Parameter 2'[Parameter Fields])
RETURN
SWITCH (
TRUE(),
SelectedTop = 0, 1,
SWITCH (
TRUE(),
SelectedCategory= "'sample'[Department]",
CALCULATE(
RANKX(ALL('sample'[Department]), [Quantity],, DESC),
ALLSELECTED('sample'[Region]),
ALLSELECTED('sample'[Approval_Status])
),
SelectedCategory= "'sample'[Product]",
CALCULATE(
RANKX(ALL('sample'[Product]), [Quantity],, DESC),
ALLSELECTED('sample'[Region]),
ALLSELECTED('sample'[Approval_Status]),
ALLSELECTED('sample'[Department])
),
SelectedCategory="'sample'[Supplier]",
CALCULATE(
RANKX(ALL('sample'[Supplier]), [Quantity],, DESC),
ALLSELECTED('sample'[Region]),
ALLSELECTED('sample'[Approval_Status]),
ALLSELECTED('sample'[Department])
),
SelectedCategory= "'sample'[Expense_Type]",
CALCULATE(
RANKX(ALL('sample'[Expense_Type]), [Quantity],, DESC),
ALLSELECTED('sample'[Region]),
ALLSELECTED('sample'[Approval_Status]),
ALLSELECTED('sample'[Department])
),
0 )
<= SelectedTop, 1, BLANK())
3. How Many and How: This measure introduces the flexibility to choose the dynamic value to rank the Top N items.
TopN value wise =
VAR SelectedTop = SELECTEDVALUE('Parameter'[Parameter])
VAR SelectedCategory = SELECTEDVALUE('Parameter 3'[Parameter Fields])
RETURN
SWITCH (
TRUE(),
SelectedTop = 0, 1,
CALCULATE(
RANKX(
ALL('sample'[Supplier]),
SWITCH (
SelectedCategory,
"'sample'[Quantity]", [Quantity],
"'sample'[Discount]", [Discount],
"'sample'[Total Cost]", [Total Cost],
"'sample'[Unit Cost]", [Unit Cost],
BLANK()
),
, DESC
),
ALLSELECTED('sample'[Region]),
ALLSELECTED('sample'[Department]),
ALLSELECTED('sample'[Approval_Status])
)
<= SelectedTop, 1, BLANK()
)6. How Many ,By What and How: This measure offers a combination of selecting a specific category and value.
TopN 4 =
VAR SelectedTop = SELECTEDVALUE('Parameter'[Parameter])
VAR SelectedCategory = SELECTEDVALUE('Parameter 2'[Parameter Fields])
VAR SelectedValue = SELECTEDVALUE('Parameter 3'[Parameter Fields])
RETURN
SWITCH (
TRUE(),
SelectedTop = 0, 1,
SWITCH (
TRUE(),
SelectedCategory= "'sample'[Department]",
CALCULATE(
RANKX(
ALL('sample'[Department]),
SWITCH (
SelectedValue,
"'sample'[Quantity]", [Quantity],
"'sample'[Discount]", [Discount],
"'sample'[Total Cost]", [Total Cost],
"'sample'[Unit Cost]", [Unit Cost],
BLANK()
),
, DESC
),
ALLSELECTED('sample'[Region]),
ALLSELECTED('sample'[Department]),
ALLSELECTED('sample'[Approval_Status])
),
SelectedCategory= "'sample'[Product]",
CALCULATE(
RANKX(
ALL('sample'[Product]),
SWITCH (
SelectedValue,
"'sample'[Quantity]", [Quantity],
"'sample'[Discount]", [Discount],
"'sample'[Total Cost]", [Total Cost],
"'sample'[Unit Cost]", [Unit Cost],
BLANK()
),
, DESC
),
ALLSELECTED('sample'[Region]),
ALLSELECTED('sample'[Department]),
ALLSELECTED('sample'[Approval_Status])
),
SelectedCategory="'sample'[Supplier]",
CALCULATE(
RANKX(
ALL('sample'[Supplier]),
SWITCH (
SelectedValue,
"'sample'[Quantity]", [Quantity],
"'sample'[Discount]", [Discount],
"'sample'[Total Cost]", [Total Cost],
"'sample'[Unit Cost]", [Unit Cost],
BLANK()
),
, DESC
),
ALLSELECTED('sample'[Region]),
ALLSELECTED('sample'[Department]),
ALLSELECTED('sample'[Approval_Status])
),
SelectedCategory= "'sample'[Expense_Type]",
CALCULATE(
RANKX(
ALL('sample'[Expense_Type]),
SWITCH (
SelectedValue,
"'sample'[Quantity]", [Quantity],
"'sample'[Discount]", [Discount],
"'sample'[Total Cost]", [Total Cost],
"'sample'[Unit Cost]", [Unit Cost],
BLANK()
),
, DESC
),
ALLSELECTED('sample'[Region]),
ALLSELECTED('sample'[Department]),
ALLSELECTED('sample'[Approval_Status])
),
0 )
<= SelectedTop, 1, BLANK())
Conclusion:
In conclusion, the power of dynamic Top N calculations in Power BI is boundless. From spotlighting top N based on How Many, By What and How. These flavors offer versatility that transforms your reports into dynamic, user-centric experiences. The journey doesn't end here; it's an invitation to explore, experiment, and discover new dimensions of insight within your data. Elevate your reports, captivate your audience, and let the "Wow" factor shine through with dynamic Top N in Power BI!
Thanks, It helped me a lot.