The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am trying to select the top 5 states and other graphs
my dataset is 1000 startups
these startups were founded in different states of course. I want to show them like
state perc
CA 25
NY 20
Al 15
FL 12
Others 28
what should I do
Try this measre:Top5 =
VAR _top5 =
TOPN (
5,
ALLSELECTED ( TableName[State] ),
CALCULATE ( SUM ( TableName[Perc] ) ), DESC
)
VAR CurrState =
SELECTEDVALUE ( TableName[State] )
VAR result =
IF ( CurrState IN top5, CurrState, "Others" )
RETURN
result
Thank you @Tahreem24
So I need first to build a perc field.
Do you have any suggestions?
I have overview[state] field, it is better to build a new column as states[percentage], states table also has [state] filed for relation.
I should count all states themselves and divide by the total which is obviously 1000. But I couldn't do this too.
Edit: Sorry It wasn't difficult, I made it
@Zana For a Perc you need to create a measure like below:
Perc =
VAR _statewise = CALCULATE(COUNT(TableName[State]))
VAR _total = CALCULATE(COUNT(TableName[State]),ALL(TableName))
RETURN DIVIDE(_statewise,_total,0)
Then follow my previous post formula to get the Top 5 and others.
is there any mistake on this row:
IF ( CurrState IN top5, CurrState, "Others" )
when I add _ in front of the top5 it works, like :
IF ( CurrState IN _top5, CurrState, "Others" )
on the other hand, I made this but how should I see the results of this function? I didn't understand how can I use it.
@Zana , refer my approach on the same
Power BI- Power BI- TOPN with Others- https://youtu.be/I_TY4hVlzAE
I give up on solving problems in these ways. I need more simple and sustainable solutions. Thanks for your helps.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |