Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How do I filter only top 3 Sales for each quarter and bring the average of them into a new table?

Hi experts, 

 

I'm trying to figure out a way to simplify this issue. What I have is the below table

 

jonnaamb_2-1597048488208.png

 

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?

 

jonnaamb_3-1597048518213.png

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

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.

 

H1.jpg

 

2. Then we need to group YQ column.

 

H2.jpg

 

3. And we can add a custom column to add index column.

 

H3.jpg

 

4. Delete the first two columns. And expand the last column.

 

H4.jpg

 

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.

 

H5.jpg

 

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.

View solution in original post

7 REPLIES 7
v-zhenbw-msft
Community Support
Community Support

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.

 

H1.jpg

 

2. Then we need to group YQ column.

 

H2.jpg

 

3. And we can add a custom column to add index column.

 

H3.jpg

 

4. Delete the first two columns. And expand the last column.

 

H4.jpg

 

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.

 

H5.jpg

 

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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

Daviejoe
Memorable Member
Memorable Member

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.