Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone¡
due to the good help I received with my previous problem with PowerBI, I have decided to ask for help again.
I want to filter the entire page by the Top N Managers wich has more AuM in order desc. I Have reviewed all the youtube videos that explain how to use the top N and have not found a possible solution. I have worked with different measures in many different ways but have not found anything that could help me.
Moreover, I have downloaded examples from Avi Singh who is a very good teacher. I think I'm close to the solution but I can't finish.
Example of the first graph above left and measurements that I have created.
-----I have tested this measure in both ways----
1.
2.
Then, i create this mesure to put in the visual filter in the graph:
I think I'm doing complicated things and I'm making a mess, and I think it must be easier than I think.
Any help is welcome, many thanks to everyone¡¡
Best regards
@Anonymous I actually did a presentation on how to do this a couple months ago, it may help you with your solution.
My solution is actually a top/bottom x (shows top x and bottom x on the same chart) but can easily be modified to do only top x.
This is the ranking measure:
Rank (Desc) of Managers by Value =
IF (
ISBLANK ( [Value] ),
BLANK (),
RANKX (
FILTER (
ALLSELECTED ( Datos[Manager] ),
ISBLANK ( [Value] ) = FALSE ()
),
[Value],
,
DESC,
DENSE
)
)
and then you create a flag measure to work with your selected "X":
Rank Flag for Value =
IF (
ISBLANK ( [Value] ),
BLANK (),
IF (
[Rank (Desc) of Managers by Value] <= [Selected N]),
1,
0
)
)
Then you put the [Rank Flag for Value] as a FILTER FOR THIS VISUAL on the bar chart visuals.
This is also shown here: https://community.powerbi.com/t5/Data-Stories-Gallery/Executive-Insights-by-Decisive-Data/m-p/181742
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
I have created a new proxy to share in order to someone can help me:
https://app.powerbi.com/reportEmbed?reportId=c6c529fe-ec4f-4265-9ae9-905c09bb6312&autoAuth=true&ctid...
@Anonymous
Can you pease share the PBIX file itself? (through Onedrive, Dropbox, Google Drive..)
Proud to be a Super User!
Paul on Linkedin.
@Anonymous
See if this works for you (I wasn't sure what calculation you wanted to see in the visual "TopN sobre Income Total"). I've changed the TopN range to 1 - 4 (assuming the rank refers to "Shops") since there are only 4 possible in the sample data.
The data is showing the values for the TopN Shops overall (ie. not ranked for each category in the visuals)
Proud to be a Super User!
Paul on Linkedin.
That is exactly what I need, thank you very much, I do not know how to thank your help.
I only have one problem with the data table, since I had to create another measure to order the shops / managers by AuM desc. The calculation of the total of the table does take into account the filtered Top N, but it shows all the Manager / shops of my table and I just want it to show me:
-If I choose 5: the first 5 managers
-If I choose 10: the top ten
So i created a new measure for tank ONLY for the table:
I had to create this new measure because the measure that you have used returns only the number 1 for all shops / managers as seen below:
@Anonymous
Sorry, I'm not sure what you mean in your last post (or who you are referring to...!)
If you are referring to the solution I suggested, you can limit the list of Shops/managers in the table visual by using a measure such as:
Selected RankX = IF([RankX Shops Overall] <=[Selected N], [RankX Shops Overall])
To give you this:
Ps.
1) The example you posted to solve this seems wrong, since you are filtering by Top 10 and you are getting the 11 top managers (if not more! Which means something is wrong with the [Overall Value] calculation too BTW - please check the Dimension Tables in the sample PBIX file I attached and how they are used in both the measures AND visuals AND the actual model relationships).
To solve this, if you want to use your measure, try changing the [value] expression in the new rank measure with [Overall value]
2) If you want to get rid of the Rank value in the "Total" row in the table, use the ISINSCOPE function (sorry, I'm not in front of my pc so I cannot be more precise. if you need help with this, just let us know!)
Proud to be a Super User!
Paul on Linkedin.
Yes, sorry for my english it's not good that i would.
The problem is that if i click 5 on this filter:
I only want to see in the table, the top 5 shops/managers with more Aum. If i click 10, i only want to see in the table top 10 managers/shops with more Aum
And the result that this table shows is:
There are all shops/managers in the table (210 more or less). But, Overall value IS CORRECT because that 39.879.306... its the sum of the 5 top shops, but the table shows all shops
Can you post the measures you have used to calculate the [Overall Value]? In the example I provided, the rows beyond the selected TopN are blank, so there os something going on in your measures
Proud to be a Super User!
Paul on Linkedin.
@Anonymous
Thanks for the file.
A couple of questions:
1) So when you want to flter by TopN managers, which field determines "Manager"? Is it "Shop"?
2) When you wish to filter by the TopN, is the TopN for the whole dataset (so when you show the results by for example "Asset Class", the TopN refers to the overall performance and not the TopN within "Asset Class") , or is each visual to represent the TopN for the category shown in the visual? - see my example in my previous post.
Proud to be a Super User!
Paul on Linkedin.
The top N refers to the managers with the most assets under management. That is to say:
-If I filter by Top 5 I want the entire page to be filtered with the data of the 5 managers with the most assets under management, I do not want data from other managers to appear that are not that top 5
-If I filter by Top 10 I want the entire page to be filtered with the data of the 10 managers with the most assets under management, I do not want data to appear from other managers that are not that top 10
In the case of the graph: AuM by Asset Class I want the filter on selector 5 or 10 or 30 .. that graph shows the AuM of those selected managers filtered by Asset Class.
In summary, I want both the top 3 letters on the right, as well as the table, and the different graphs ONLY to show me the data of the Top N filter managers that I have selected. And that data is the relationship between the AuM column and different columns with other categories
I have created a proxy eliminating the real data and eliminating columns to be able to share the powerBI and the equivalences are the following:
Shops = Managers
Seller = Fund
Sales = Aum
Thank you very much for the great help
Hi @Anonymous
You might consider providing your dummy pbix that would be folpful for us to investigate it further.
You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.
I have created a proxy of a data table similar to the one I use (although with much less categories). My idea is to create a dashboard page in which I can filter by Top Shops: 5-10-20-50 based on the shops that have had AuM 4Q/20. I would like all the graphics to be filtered by said top N shops.
I think the problem is that i dont know how to create new measures to filter by top N shops by AuM ( DESC), in each graph.
Thanks everyone again¡¡.
@Anonymous
I think I understand what you are trying to achieve: filter all visuals on the page by using one report page filter, right?
If so, you need to include a filter statement (or reference statement to the "TopN" selection) in all the measures in each visual. Otherwise the visuals will not be filtered by the global filter; only the visuals which include the "Shop" Dimension as a filter context will get filtered; all others will not.
In the following example, I've created a meaure to rank Items.
First, the model itself:
I want to see only the actuals and target for the top 3 items dimension.
As you can see, if a visual does not contain the Item dimension, the rank returned is "1" for all the other dimensions ("Channel" and "REF Dimension"). These visuals will therefore not be filtered by a page filter referencing a "TopN" Items; you need to specify the Filter context in the measures used.
In my case I have created measures as follows:
To filter each dimension by its top 3 items (so the result would respect the top 3 items per each dimension), I use the following RankX measure:
RankX actuals by item = RANKX(ALL('Item Dim'), [Sum of Actuals],,DESC,Dense)
Which Gives me this:
If, however,I need the top 3 Items for all dimensions (Ie the top 3 of all), I would use this measure:
RankX actuals by item (ALL dimensions) = CALCULATE([RankX actuals by item], ALL('Channel Dim'[Channel]), ALL('Ref Dim'[REF]))
Which gives me this:
You can then use either of these ranks in the filter expression of measures. For Overall 3 Top Items:
Actuals Filtered by RANK Top 3 Items = CALCULATE([Sum of Actuals],
FILTER('Item Dim',
[RankX actuals by item (ALL dimensions)] <=3))
Target Filtered by RANK Top 3 Items = CALCULATE([Sum of Target],
FILTER('Item Dim',
[RankX actuals by item (ALL dimensions)] <=3))
Check the results:
If you look at the visuals which do not have the Items Dimension as a filter context, the rank returned for all rows is "1". The global filter would not work. You get around this by using measures which include the relevant filter context for the dimension you wish to filter by, if that makes sense (see my sample measures). When you include these measures with other dimensions, you will get the correct results (in the example, it's the columns and values in the tables/matrix with green backround, where you get the result for the top 3 overall Items and how much the contribute to the Channel or Ref Dimensions).
In your case, since you want it to be dymanic, you need to substitute the "<=3" in the filter expression in the measures with the SELECTEDVALUE expression for the TopN slicer value.
I hope that makes sense!
Proud to be a Super User!
Paul on Linkedin.
Thank you very much for the full explanation, but i dont know what i'm doing wrong, i'm gonna share a powerBI proxy and put the link here to see if someone can help me because I've already tried everything.
@Anonymous , Create the second measure like and try
calculate(if(<Condition>) , values(Datos[Asset Class]))
Refer this I have used Rank filter there.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |