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.
Hello, I am pretty new to Power BI and Dax, and need some help showing the Top N values in a table as well as on my page.
I have a fact table with all of the product identifiers and amounts, and a dimension table with the product identifiers and their respective names. I have created another table in Power BI called Top Table with column TopN and values 5, 10, 15, 20, 100.
In my fact table i have summed the amounts using Total_Amount = sumx(Results, Results[Amount]) then i have a rank function to rank the products by amount Rank Amount = rankx(allselected('Unique Codes'[Product Code],[Total Amount],,Desc). This is working as it should ranking the products by amount. Now i would like to have a slicer that shows by TopN column where i can choose one of the values and the table as well as any graphs i have on the sheet only show the TopN values. So if i choose 5, the table would show me the top 5 products by amount, and any graph would be filtered to only the top 5 as well. Then top 10 would show the top 10 etc.
I have read a lot of things, but cannot seem to get it to work for me. It would make sense that i would want to create a virtual relationship between my fact product table and my new topN table on the rank, but obviously my TopN table does not have all of the ranks in it.
Thanks for any help.
Solved! Go to Solution.
http://www.daxpatterns.com/parameter-table/
To see an example go to the bottom of the page at the above link where it says Downloads
Download the Excel 2013 zip files - extract all
Then Open PBI - File - Import - Excel Workbook Contents - Select ParametersTable - TopN - Excel 2013.xlsx - Open
click Start on the warning "We don't work directly with Excel... blah blah..." - Copy Data - Migration Completed - Close
Save the File as pbix - you may have to close and open it again
Now create a Table - place Product and TopSalesAmount in the table visual
then create a Slicer with the field from the Tp table - select any value from the slicer and see how the Measure reacts!
Hope this helps!
http://www.daxpatterns.com/parameter-table/
To see an example go to the bottom of the page at the above link where it says Downloads
Download the Excel 2013 zip files - extract all
Then Open PBI - File - Import - Excel Workbook Contents - Select ParametersTable - TopN - Excel 2013.xlsx - Open
click Start on the warning "We don't work directly with Excel... blah blah..." - Copy Data - Migration Completed - Close
Save the File as pbix - you may have to close and open it again
Now create a Table - place Product and TopSalesAmount in the table visual
then create a Slicer with the field from the Tp table - select any value from the slicer and see how the Measure reacts!
Hope this helps!
I have it working on my data, but I would like to do something more, if you could help, that would be great.
I have five tables right now: Main Agency, Agency, Location, Results, and Top (created from last response)
Main Agency contains all of the major agencies and their codes. Agency contains all of the major agencies and each of the branches codes. Location table lists all of the branches and their address (full address). Results is the amount that each agency collects. Some agency locations have not collected anything, so they could be in the location table and not in the results table.
I am using the Agency table and Results table to compute my dynamic Top N and it is working great, but when i want put in the branch, it is throwing things off.
Here is a sample of what i have so far that is working with the dynamic Top 5
Agency Code Agency Name Amount
11 Agency D 2000
2 Agency A 1900
5 Agency B 800
10 Agency E 600
9 Agency F 559
Here is what i would like to do
Agency Code Agency Name Location Amount
11 Agency D Location 1 1000
11 Agency D Location 3 600
11 Agency D Location 2 400
2 Agency A Location 1 1900
5 Agency B Location 1 800
10 Agency E Location 1 400
10 Agency E Location 2 200
9 Agency F Location 1 559
But what it looks to be doing is ignoring my top 5 slicer because it is listing Main Agencies that are not in the top 5 in terms of amounts collected.
I have the relationships created between all of the tables, but am not sure how to make my dynamic TopN affect each one.
Here are my current measures in Results for the Top N ranking.
TotalAmount = SUMX(Results,Results[Amount])
Total Amount = IF(HASONEVALUE('Top'[Top]),IF(RANKX(ALLSELECTED(Main_Agency),[TotalAmount]) <= VALUES('Top'[Top]),[TotalAmount],BLANK()),[TotalAmount])
How can i make that Total Amount measure impact all of the tables that are linked to the Results table?
Here are the STEPS... (you can rename all/any of these)
1) in the Results table create a COLUMN
Main Producer Related = RELATED(AgencyCode[Main Producer])
2) then modify your TotalAmount MEASURE
TotalAmount = CALCULATE ( SUM ( Results[Amount] ), ALLEXCEPT ( Results, Results[Main Producer Related] ) )
3) then modify your Total Amount MEASURE
Total Amount = IF ( HASONEVALUE ( 'Top'[Top] ), IF ( RANKX ( ALLSELECTED ( Results[Main Producer Related] ), [TotalAmount] ) <= VALUES ( 'Top'[Top] ), CALCULATE ( SUM ( Results[Amount] ), FILTER ( Results, [Rank Measure] <= VALUES ( 'Top'[Top] ) ) ), BLANK () ) )
And here's the result
Hope this helps!
This is doing exactly what i was looking for. Thank you for the help. I will try to do this on my dataset and let you know if there are any problems. In the meantime i will mark as solved.
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 |