Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi experts,
I'm trying to figure out a way to simplify this issue. What I have is the below table
And what I expect in the end is the average of top 3 sales values only for each of the YearQuarter, could you please suggest?
Solved! Go to Solution.
Hi @Anonymous ,
We can rank the sales within the group using Power Query Editor to meet your requirement.
1. We sort Year Quarter Ascending, and sort the Sales Descending.
2. Then we need to group YQ column.
3. And we can add a custom column to add index column.
4. Delete the first two columns. And expand the last column.
5. At last we can create a table visual, put the YQ and Sales in it, then put the rank in filter on this page and configure it less and equal than 3.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @Anonymous ,
We can rank the sales within the group using Power Query Editor to meet your requirement.
1. We sort Year Quarter Ascending, and sort the Sales Descending.
2. Then we need to group YQ column.
3. And we can add a custom column to add index column.
4. Delete the first two columns. And expand the last column.
5. At last we can create a table visual, put the YQ and Sales in it, then put the rank in filter on this page and configure it less and equal than 3.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Many thanks for your detailed explanation. This really helps. Brilliant effort!!!
Also for my reference, could you please post the links to the custom column formulae where I can learn more? Thanks in advance.
Kind regards,
Ambareesh
Hi @Anonymous ,
The custom column formula like this,
Table.AddIndexColumn([Count],"Rank",1,1)
The complete M language is as follows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5BCsAwCETRu7jOQseYtsfoOuT+12gFhQjZyePDOCcxCzUCg18/htFqriiKUD1qP6oV1dBRlEOvVJ+1/OEumu2za49WuGi0Irtqtiiare6Kv10f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, YQ = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"YQ", type text}, {"Sales", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"YQ", Order.Ascending}, {"Sales", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"YQ"}, {{"Count", each _, type table [ID=nullable number, YQ=nullable text, Sales=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Rank",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"YQ", "Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "YQ", "Sales", "Rank"}, {"Custom.ID", "Custom.YQ", "Custom.Sales", "Custom.Rank"})
in
#"Expanded Custom"
You can also download the attachment for reference.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Also, just FYI,
when I'm trying to group the date-type field in my actual data, but I don't see the sort happening the right way. But I'm using a workaround to fix this issue by creating custom columns which are not date-type. This is just a heads-up to all who see this post.
Thanks,
Ambareesh
@Anonymous , this can help
https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
Do either of these help you?
https://community.powerbi.com/t5/Desktop/Top-N-filter-for-measure/m-p/241560#M107217
https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/
Proud to be a Super User!
If my reply gave you the answer can you accept it as the solution please, or accept @amitchandak if that gave you the answer.
Thanks
David
Proud to be a Super User!
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |