Introduction to Power BI

Power BI is a powerful tool for connecting to diverse data sources, transforming and modeling data, and crafting interactive dashboards and reports. It offers users the ability to extract valuable insights from their data and effectively convey these findings.

Creating the Visualization

We will build a visualization that includes the following components:

• Top N KPI Card: Displays the total sales for the top (n) products based on sales.
• Value Parameter Slicer: Allows users to select the N value for the Top N metrics.
• Top N Table: Lists all the Top N States alongside their respective sales figures.

Step 1: Creating the Field Parameter

In the Report view, navigate to the Modeling tab, select "New parameter," and then choose "Numeric range." For the name, you can use "Value Parameter," set the data type to "Whole number," and adjust the minimum to 1, maximum to 20, and increment to 1.

Next, select the slicer and go to Format visual → Slicer settings → choose "Tile."

Step 2: Constructing the Top N Table

Head to the Visualizations pane and select "Table" in the Build visual section. For columns, include a categorical column and a numeric column from your dataset. For instance, use "State/Province" as the categorical column and "Total Sales" as the numeric column.

Note: If "Total Sales" is not already in your dataset, you can create it using the DAX formula:

Total Sales = Sum(Table1[Sales])

Now, select the table and go to the Filters pane. Expand the "State/Province" section, choose "Top N" in the filter type, set "Show items" to 4, and use "Total Sales" for the "By value" field before clicking "Apply filter."

Step 3: Developing the Top N KPI Card

To create the Top N KPI, use the following DAX formula:

Top N Products by Sales = CALCULATE([Total Sales], TOPN('Value Parameter'[Value Parameter Value], VALUES(Table1[State/Province]), [Total Sales], DESC))

The CALCULATE function modifies the context of the data being evaluated. The TOPN function retrieves a specified number of top rows based on a designated expression.

For further understanding of the TOPN function, check out this insightful video:

Next, you should also create a dynamic title for the KPI card:

Sales Title = "TOP "&SELECTEDVALUE('Value Parameter'[Value Parameter])&" Products by Sales"

To finalize the KPI card, select it, navigate to Visualization → Format visual → Visual → Category label, and turn it off. Then, enable the Title and set the Text to use the Sales Title field.

Conclusion

Creating visualizations in Power BI is a rewarding process that allows for the effective presentation of data insights. If you found this guide helpful, consider supporting my work by buying me a coffee—your support is appreciated but not obligatory.

To further enhance your Power BI skills, check out these additional resources:

• Average Purchase Value (APV) KPI
• Return on Marketing Investment (ROMI) KPI
• Dynamic Area Charts with Time Period Slicers
• Customer Acquisition Cost KPI
• Level Up a Bar Chart with a Trend Indicator

For ongoing updates, subscribe to:

👉 Power BI Publication

👉 Power BI Newsletter

Join our Power BI community at Power BI Masterclass.

References:

To deepen your understanding of using DAX for Top N calculations, watch this related video: