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'm trying to get my matrix table to show these values:
when I filter the table to show the the top 1st name it filters the value to:
Solved! Go to Solution.
https://goodly.co.in/top-n-and-others-power-bi/
I used the following blog to help me with my problem. Had to tweak the code a lil to match the measures I calculated but it works as expected.
https://goodly.co.in/top-n-and-others-power-bi/
I used the following blog to help me with my problem. Had to tweak the code a lil to match the measures I calculated but it works as expected.
Hi,
Share the download link of your PBI file.
Hopefully this screenshot will suffice for you.
Hi,
Do away with the Over hits measure. Revise the % measure to
=divide([Measure test],[Hit])
Hope this helps.
No it still recalculate the percentage to 100%
I want the percentage to stay the way it was before the table was filtered
Hi,
Share the download link of the PBI file.
Hi @ArT101 ,
I created some data:
Here are the steps you can follow:
1. Create calculated table.
Sum =
var _table=SUMMARIZE('Table','Table'[Name],
"Sum",SUMX(FILTER(ALL('Table'),'Table'[Name]=EARLIER('Table'[Name])),[Amount]))
return
ADDCOLUMNS(
_table,"Rank",RANKX(_table,[Sum],,DESC))
2. Create measure.
Measure test =
SUMX(FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])),[Amount])
Overalll Hits =
SUMX(ALL('Table'),[Amount])
% =
DIVIDE(
[Measure test],[Overalll Hits])
rank =
MAXX(FILTER(ALL('Sum'),'Sum'[Name]=MAX('Table'[Name])),[Rank])
Flag =
IF(
[rank]=1,1,0)
Before filtering:
3. Place [Flag]in Filters, set is=1, apply filter.
4. Result:
After filtering:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you greatly for the help. My only issue is the summing of the amounts. I am aware that my first message was vague with the explanation because I thought the process would be much simpler then the method you provided.
So for more information. My table is basically showing the number of times a match has been made against specific names provided by a user, which is what is showing in the name column. Even though multiple names can be duplicated, their Id's make them unique. So while I do want to be able to know what the values of the name A adds up to, I still want to keep them as individual row values. Like the image shows:
This is my code used:
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 |
---|---|
106 | |
102 | |
82 | |
62 | |
56 |
User | Count |
---|---|
254 | |
119 | |
115 | |
99 | |
71 |