How to make an interactive TOP 10 Chart with Power BI (using parameters)

Рет қаралды 9,271

Күн бұрын

🏆 Participate in my Dashboard Competition 👉 bit.ly/3xgC1WB
~
If you make dashboards or reports with Power BI, you often get questions like, "but I want to see top 10 products, not just 5" or "can we see top 10 store names instead of all?" from your stakeholders all the time.
In this video, let me present you with an elegant and simple solution to such problems.
A dynamic TOPN chart
What is TOPN?
TOPN is a DAX function which returns filtered table with just top n (or bottom n) values based on a criteria.
For example: We can use it to filter top 10 products out of all products based on total sales.
What is a TOPN Chart then?
A TOPN chart uses the same logic but displays top 'n' items based on a dynamic criteria and evaluation context on the screen.
How do you make it?
We use "parameter" feature of Power BI along with your existing semantic model to create dynamic topn charts in Power BI. Watch the video to understand the process and implement it with your data.
⏱ IN THIS VIDEO:
================
0:00 - What is a Dynamic TOPN Chart?
0:20 - Using Parameters to create dynamic topn field
1:25 - Creating Dynamic TOPN Chart with the DAX measures
3:56 - Dynamically filtering the table (or chart) with filter panel
4:43 - Converting the table to chart
5:20 - Big announcement 💥
👩‍💻👨‍💻 MAKE POWER BI DASHBOARD AND WIN 🎁
I am running a Power BI Dashboard competition this month. Check out the details and join the competition here 👇
bit.ly/3xgC1WB
💡 LEARN POWER BI AND SHINE AT WORK
If you want to learn Power BI and grow at work, please consider my online Power BI classes. I've distilled my years of practical knowledge and real-world experiences in to this course. The step-by-step instruction combined with best practice guidance will help you unlock the true potential of Power BI in short amount of time.
Check out the course page and enrol today 👇
chandoo.org/wp/power-bi-course/
~

Пікірлер: 32

@unnikrishnansanthosh 2 ай бұрын

your ideas are amazing, thank you for sharing

@rabiraul623 2 ай бұрын

I love your voice You are Amazing 💚

@sakeenasammi1768 2 ай бұрын

I'm amazed that you bring such concepts and explain it in simplest way. Thank you....

@chandoo_ 2 ай бұрын

You are welcome :)

@nandakumar473 2 ай бұрын

it's really helps people who want to grow in power BI. Thanks!

@SaaSLTDDeals 2 ай бұрын

Wow, such a detailed breakdown on creating a Dynamic TOPN chart! The step-by-step guide is super helpful for PowerBI users. Can't wait to try this out!

@chandoo_ 2 ай бұрын

You're very welcome!

@trevorbbeairsto 2 ай бұрын

Really learning a lot from you, thanks!👍

@chandoo_ 2 ай бұрын

Glad to hear it!

@chrism9037 2 ай бұрын

Great video Chandoo!!!

@chandoo_ 2 ай бұрын

Glad you enjoyed it

@user-kv4jq7ve6b 2 ай бұрын

This is amazing. I just want to understand how you created that Top 10 products bring to certain % of all our sales. And the amount out of total amount?

@chandoo_ 2 ай бұрын

Thank you. Here are the measures I've used: Top Products Total = CALCULATE([Total Sales], TOPN([Top Products Value], ALL(Products[Product]), [Total Sales], desc)) top products proportion = [Top Products Total] / [Total Sales]

@haricoolharan_official 2 ай бұрын

Nice sir

@pgkannan 2 ай бұрын

Excellent thank you

@chandoo_ 2 ай бұрын

You are very welcome

@tejaswic5339 2 ай бұрын

Hi Chandoo, recent subscriber here and really love the content! Can you please help me out with this - How do I find the salesperson with the highest variance for each month?

@chandoo_ 2 ай бұрын

You can use TOPN for that. Assuming you have a variance measure, =topn(1, all(salespersons), [variance], desc) should give you a one row table with the salesperson. Send that to a function like selectedvalue to see the name.

@user-lk2uv5yo9r 2 ай бұрын

Thanks, good solution, but not good if you would like to put additional field in chart "Legend" field (for example Department). It will stop to work (will show all and not TOP N).

@chandoo_ 2 ай бұрын

Thank you. Read up www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/ to know how to deal with multi-level ranking.

@user-lk2uv5yo9r 2 ай бұрын

@@chandoo_ Thanks.

@ankittiwari4371 2 ай бұрын

Hi chandoo, just need your guidance on one thing I am an finance professional with 3.5 years of experience is data analytics a good course for me or is there any other good corses. Pls suggest

@indrajaravi6945 2 ай бұрын

What are you looking to upskill in?

@ankittiwari4371 Ай бұрын

@@indrajaravi6945 Just want to upskill myself in finance field only

@chandoo_ Ай бұрын

Hi Ankit... There is no easy answer for this. What is good for you depends on many factors. I suggest learning data analysis for a while, try a project or two and see if you find it interesting + challenging. Don't give up at first or tenth hurdle though. Push thru for a while and if you still feel negative about it, then pivot and do something else. All the best. If you want a course recommendation, I suggest joining my Excel or Power BI courses. They are top notch and excellent value for money.

@sunraviprakash 2 ай бұрын

How do we add "Others" so that chart shows e.g. Top 5 and adds up rest into "Others"

@chandoo_ 2 ай бұрын

For that you need a separate dimension table with all products + others row. Then the logic would be same, except, for others, you want to always "show". Plus instead of visualizing the [total sales], you would make a new measure to calculate total sales for the product or add up the rest for others.

@sunraviprakash 2 ай бұрын

@@chandoo_ Thanks very much

@xistrik3rix 2 ай бұрын

How difficult is this to do in Excel with a data model?

@chandoo_ 2 ай бұрын

Much easier to do in Excel. You don't even need data model. TAKE(SORT(...), n) will give you a dynamic array of what you need. Just feed it to the chart and done.

@xistrik3rix 2 ай бұрын

@@chandoo_ thank you so much!