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!
I am interested in knowing how can I create a Top 5 + Others in a database. The others value's sum has to appear in line number 6. Therefore, the chart only has to have 6 lines as you can see in the second picture. Please, help me!
Here you can find the picture of what I need to do===>. Please, help me!
Solved! Go to Solution.
Hi @cecytabv
The basic change you need to make is to modify the filter context in which the TOPN function is called to be just the latest month.
This way, you will see values for all months, but the TOPN is determined only in the latest month.
The measures should be modified to something like:
Sales Amount Top = CALCULATE ( [Sales Amount], KEEPFILTERS ( CALCULATETABLE ( TOPN ( [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount] ), LASTNONBLANK ( Calendar[Month], 0 ) // Note: Assumes Calendar[Month] has a natural sort order // Could possibly use LASTDATE for a date column ) ) )
and similarly for the Other measure.
Hi @cecytabv
Have a look at my post here:
http://community.powerbi.com/t5/Desktop/Top-10-Other/m-p/52120#M20971
(There is a link to a pbix there)
It is pretty much the layout you're looking for - may need slight changes to control when totals are displayed.
Thank you Dear @OwenAuger, it worked very very nice, but I have another issue to be solved. I am drawing a monthly evolution chart, so I need to see the last month's top 5 evolution. In conclusion, I have to visualize the orange highlighted institutions' evolution.
Do you have any solution? Thank you.
Hi @cecytabv
The basic change you need to make is to modify the filter context in which the TOPN function is called to be just the latest month.
This way, you will see values for all months, but the TOPN is determined only in the latest month.
The measures should be modified to something like:
Sales Amount Top = CALCULATE ( [Sales Amount], KEEPFILTERS ( CALCULATETABLE ( TOPN ( [TopN Selection], ALL ( Sales[Customer] ), [Sales Amount] ), LASTNONBLANK ( Calendar[Month], 0 ) // Note: Assumes Calendar[Month] has a natural sort order // Could possibly use LASTDATE for a date column ) ) )
and similarly for the Other measure.
How do I change this code so that the TOPN is computed for across all dates in the visual instead of only for the last month?
Dear @OwenAuger could you help me with the complete solution, please?
I do not know how to make the Sales amount others measure. Also, what else do I have to do to solve this problem?
Could you try to solve it completely?
We are very near to the solution. Thank you.
This is what I am trying to get.
Dear @OwenAuger, attached you will find my pbix file wich is incomplete yet.
https://1drv.ms/u/s!AoBYecoXQCY6iCZl_QPtD3kXVqMT
Thank you!!
Hi again @cecytabv
I have constructed some formluas that do what you want. It was actually a little more complicated than I expected, and I have used variables to make it clearer.
In both of these measures:
Have a play with that and see if it's doing what you expect.
Sales Amount Top = VAR LastMonthWithData = CALCULATETABLE ( CALCULATETABLE ( LASTDATE ( vMes[IdFecha] ), Data_PanelClienteExterna ), ALLSELECTED ( vMes ) ) VAR TopInLastMonth = CALCULATETABLE ( TOPN ( [TopN Selección], ALL ( Data_PanelClienteExterna[CodigoInstitucionOriginal] ), [Sales Amount] ), LastMonthWithData, ALL ( vMes ) ) RETURN CALCULATE ( [Sales Amount], KEEPFILTERS ( TopInLastMonth ) )
Sales Amount Other = VAR LastMonthWithData = CALCULATETABLE ( CALCULATETABLE ( LASTDATE ( vMes[IdFecha] ), Data_PanelClienteExterna ), ALLSELECTED ( vMes ) ) VAR TopInLastMonth = CALCULATETABLE ( TOPN ( [TopN Selección], ALL ( Data_PanelClienteExterna[CodigoInstitucionOriginal] ), [Sales Amount] ), LastMonthWithData, ALL ( vMes ) ) RETURN CALCULATE ( [Sales Amount], KEEPFILTERS ( EXCEPT ( ALL ( Data_PanelClienteExterna[CodigoInstitucionOriginal] ), TopInLastMonth ) ) )
Regards,
Owen 🙂
Hi again @OwenAuger. It worked very well, but I cannot draw it. It happens because I have Top and Others in another column. Therefore, It does not work with bar chart. Do you have any idea about how can I concatenate them?
This is what I need. Thanks for your help.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |