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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Count number of times store is in TOPN - Calculate a top 3 of a top 3

Hi, 

I want to find the top 3 stores of the weekly top 3 stores in the selected period (data selection in the slicers). 

  1. First I need the top 3 stores per thursday week.
    Calculate, stores that have the most stores claims (distinctcount document id)
  2. Next I want to know from all the weekly top 3 stores, which 3 stores appear the most often in the weekly top 3 stores.
    Count number of times the store name appear in the weekly top 3.

The first top 3 I manage to do with a TOPN measure. But the second Top 3 I don't figure out how to get it.

 

Attached I included a pbix file with example data.

Hope someone can help out!

 

1 ACCEPTED SOLUTION
MargaritaG
Resolver I
Resolver I

Hey, 

To your existing query (named it Data) I would add Date table, with weeks column.
Then a measure of Amount should be created = SUM('Data'[Amount])
Then a rank to be added e.g.

MargaritaG_0-1644391195311.png

Then another (3rd) measure created with the purpose of 
1- summarizing per store, week, amount, rank 2 - filtering only those that are in top 3 and 3 - calculating rows of top 3. 

MargaritaG_1-1644391347156.png

at the end I placed this measure in table and can see that there are two Stores that have same score

MargaritaG_2-1644391416032.png

This is something quickly put together. If you want to show best performing store(s) you could also add a measure to concatinate those that have max score in Top 3 measure

MargaritaG_3-1644392913243.png

 


Hope this helps

 

 



View solution in original post

2 REPLIES 2
MargaritaG
Resolver I
Resolver I

Hey, 

To your existing query (named it Data) I would add Date table, with weeks column.
Then a measure of Amount should be created = SUM('Data'[Amount])
Then a rank to be added e.g.

MargaritaG_0-1644391195311.png

Then another (3rd) measure created with the purpose of 
1- summarizing per store, week, amount, rank 2 - filtering only those that are in top 3 and 3 - calculating rows of top 3. 

MargaritaG_1-1644391347156.png

at the end I placed this measure in table and can see that there are two Stores that have same score

MargaritaG_2-1644391416032.png

This is something quickly put together. If you want to show best performing store(s) you could also add a measure to concatinate those that have max score in Top 3 measure

MargaritaG_3-1644392913243.png

 


Hope this helps

 

 



Anonymous
Not applicable

I don't know how to add a pbix file... 🤔
Here a copy from the test data in advanced editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZ1brl23DYanUvi5SHSX+Jq+BmjfgzwEqIEacBLAbifQ8XRUHUkpce99jhf/9RNpgACGfT5TEinepCX/9NOHWnoWaf3Dnz+U71P+vqSS9df//fd//lRy01/llHJLq+gvf/jyr69fP37+8POfD1dKWgtwVVLA5ToAl6UEXK0Zcd2wVqTvX/7lH798+fzxy++fXmAbBQqsj4G2MdoW+OPvv/76Tl4brSEsT47NUtFyfldF+ARXzfsn6lVg6kxgzWvucTY3zv4SN/f8f/z0+fPHFzVqKYia0XLWsVpFZK10mENSAtigVMupbapfhZVKJtdyGR1Qiyq85VkXoFpjopQqAJotWMbW0xIAlsmtpPV8ftatSJoR15C8HmHr6PWKnZWi2ER669yn6E7tA0l7G2Ta2N9++fLp6xtUC4IWZTpcfVujexMZQ5BhSUDNjoTVp43oFt7UX7/+8+Nvf//4DhvQQnINuJWQSZYVYaUhrAecKKq/Pa7LH1iI7guE5cm5rh5hIu4tenh1KzQrghqFOoR65gOsdXUkSyKuJSSu8Q2qHqghcWVmqrbtgCDXIywjtZVSAm7A2ZUeDLOb/yTqhqvSZTSAVe5c+5jH/Lyd0A2u2ELSchoMm1X/RH933fjW29gxaz8O5QrmHEWdqaFxAbK85Wx+I8xmG8GJ6xRaCUri9qVYR2tSesQJFpe5Xc6ezjZwXH2bHLIU5U6e6zjh7muOIgPpfHKDVpd+ZiLXPGPS6Sl2JnLFAp+u2HHOV2zwxVTspHNXLM8RcD0hcVwFSyuXhaS99g/cdatYAPEY3eOKYWk5wCw/9NIKxVopcJCLehTFbuZGs+zVLPT7uT2l3fmTpX4WCmSuYekgkLpzUCCtMU8FeOXKqCGHVBdsccXgJjDNMG4ltCal8mGK/tH5iXzjnG92j3IWfB0XxGQFzQ9cwZq5s5VcGwRL4vm9aJI5ITiDtWli1foVbI13G0T9O5SYewgWPMfGPbWCx2z8ULl1Sy8TTjFHA214afKKwG56voLBdpJRO7TT3KjjVc42uJ8g66iMlJP5IdeJIdm7Us2kXSmhm0K5bn7hyg0awzZnjQI3ykm37wZlIrDQ0D70B20qV+5pn3dOWxPxgiW+xel7dB0X7Af7THWx0WxwJDxLWhaNpOUN1Ecb1Lw3uOBQm1AnpWA/qSSwN5rbKTgy1MiigWZz2HQCcb1ica/m2M1+UgOHqsgvy4Gc/sE4C+oblEHZsdHTbvFo1L0dWQsWiLKSRfWQO6QWjWybM+u4cu1VAd6Bxf5qP0zuN1RZFqbd0gzaHlJw2V/thsrNZq8n1kXANeu/O0641fQ84DB5src5qVgTnNO9BDWYv4tcTV7dfMq1R10C3a9uf7NvwdPmxgEnAiWwGfWlAgUKN2/NzxoUmEmzVKme8fx4b2qU3OwvdguambiarXt+pXi+rlwxb+KOGLilKXezKNzSlJtYC88y+Q5sXaCd1cGDaGmjYgPlffUN2pgcOAMNtoGHaubFwJmw6oOR6h48i+A684Odgik3LIv0J0WsYtb1fBRPjuPesKrtC+JK425ba6vTr/UCgzyx6n9YYqbhXrGJBXa6onV0c8BOHm+ijjqHlSyuHRpsYAUt572C/GR41NVP+9VxfDdpNWIDcuPsgSZEJpwgb3Ntzg5FXIufcy1Ns1G3Ls/wcpc86cTPWa0ja6I1noJS8FATV37TYg0uaktciW2nXnBReZq/z3qh0bRghlmsl+atlDqMVm4mmAfndr9y/8C8ZqS8zFPOXOaVK0F+qKCFtSv4lubfgGLWlF2/v0bloaIToj1K83saZuNXcvGcrZdmXurKjYgbeJJBjtj1f4GL8zpvg+rfp3sZyhPqvbtkK39c8zLIg9V5m2X9wWa8gi1DiUKPnTdnIdoJTDwL7tKtY/9HmshKrQbFvcXt4xd++Pjl86ffnpRWMQ3KqlztWo7YT/jJ8ZimEc06/USgn9wO2gNRg91gUWwuvJL0/HLsCApV/rrFgrFlTdmS/pjrVe50SB0XJQiai9oVsCsYtIKGlAwFBkmsciexdlznyaFypxDzXLAwUk8uBxYm4JZdxbty/HRQOSlQXhRbFDynWh6svACdOocJZ5h5zjWTNDhF2gaa2c6G2IIiapyK31EyGFXHjfKE7iLlKuYy54adiZdrOz2IYFMlVsQ17t+VO2ctjhN6lrw520T+uCBQ+ZgCwedR+R2n84MT5Ocam8MLGpSsc7YCFyZyZwpWKDAH2bmCJy1yID/TUq4nONLCB7o0ag7IZV5iKTix7nnbYRU7EPS2RuPfKjXBBX0mkTfY7GerlWtDPMxaJVkDz5E1qHYkWwfPgcEtaI0sdh/zj4N1CJzk22nB7SzV3SWEjqge2OSCQlNwx2nIkHM1tdz1fxl5XKAjw9MtLestjrreMe9xqzzbyP54gyb3stKpbh0X9MZFklmPmyELhqJGB9cl8G+aJCSoiUo3sYb6UfBq8gbSBu3mvT8SoXmXas867uXasuJ91c1ZmuCuflO/qJwMKG+R9GJTAqmcSX6hCVA+3s9j/NKFglbqepCfMG8QD/R1k+UWfIR8f5c4kmhNEr+gNB9VTvDaFBpIp/5JwUsTcLk2OM634hpt+M2dotRxwm5AzKzp/Zmev5fKt2DWCm1CsNBoMbOqXiBYuQaz+sKFwcDjHxTLTDTDnEUr/YFnydKFzS3IcW0UdYcFYe/8qPtQZJY+zDe5Ph43tbLsgMJxLWj+KmllvyP5VVPlmvn7K1ffOO+eimZRCVHvDlLAqsijaLpSPZydPIo0ty5B0qbktKTUdWJLQGpW1yDZgsCk4OmLeuWnCLQuHltVzNnJq9chj4TKFchNHgmVO8UxGCfLgJTrBc+PtlPn/lbi2I1r+wbr0rplXK59+/Sk98of3czGXfkO0spZVSwc7Kt8unFsCp5LJX60g95IULBNKDHzo6YNyg3II3Bdj+3ov0mgEVG5Cqe4WMdyVkmW4/t7+4EBiH326cDKvyhRUGxHOYsj7q31fpKz4hqyodGooZrLuG0B35PrrJgXOuiaKnf+bjBYGkZbl7PrHBdGqK5/Bue4otVR0kK3P92IyGY95Op6pbwoUe70yB1XAtfYx5pQID+hUk5OM8NxmTf4NnjSbA/yL7OnusbThXVga4FErWkaXBveH1IwF7iovFU3+0rHTfkpvs6aoKn2lTscKI82o3XL7b5PBfmom1GOtiyluGCZfxqpnFgv9soNXugpZyO6DpNTfdk9gqs0lu2NVa3te4XoiZFij/tnV4z2SRRbBU3snVn6Ic5qX+qXKyTctpSTDrjKP99Qzr6ZunJRqTWbXeBxXBCqlTN37ayE5wbKCZInPMDP9uiqX40rxDrSQubXHk6nGnE96P5N1cNAaoh6lQraubcbaY4K1zlHbpBkXUPFZoYGk2m4nctu7zv7pJtB0ywoi/epVVY5C1evXOeOffVSEMdNU6nj+K4UP5na2DmQvWKF36jbnCBxwc1r5dpC3OIbdnU5zSY3zKAmW4/8yokLVDfsR524wB+t/X0I4Pg9rI0d1+4thX0UpJh9/eNUHqzJsI8xnLREA4lmYhb/r3vneavtdo+vabconcAox13qVjAYiZTSTorYXGAgG12hE5ivEHlAQ5l+NH5lKjlN2FCHo0tR/0XJicgS+Vmp9mSEG2ekA9Gqb6KxBjFPuV6hBnjwUm4hJbyOIW+wZt+iO3EzGGazZg8xFLSHFIOz419UKmZf4Xpp1FyaHQk46nVhBG5YxQSNkefr8ggIbmbkRaSVcsmP53++hWiLfaVdAQOsBImKgpZvOHnUSDb2fBHpW3n8m5HNnTT/ymUetZTr57cdx8+AlBujAo60S84zAIgpvDeztHyYj/c3ULaBDFIZ63JfGVb2KDTPQcwV4kmwYitXgPF75hurHQ4x5E4zw3GDuq21b1KjZXy9+nBnyrmOvAD4+hwcbtONNbQsk8XwTS24KpWd2ii2Eppc7oE0e1fEaZy5rX1fpiMNVP6V7OYEDbIN7oRy69BSKr8YuTkob0XDHAstCv/ycGkqWNGWazzZWyXZ8fy6K+mwOSs2BWCZh2HlyvNJmEueXjinaRSSFx1lrdItGXQC+VNYq4w1GppgYy33jQlaTnrHRYvOMR5vhHxL0ZJuY2sALKj/d42bkLjMHz/bXEXy+Ff/q6Z+vNwVe71OeKc7BY+RXcHo6tiquvUWmiB9DEGxUSfCaONN40g+V7iuGK8FN5Y7wrg7qr1mpIR3/sHHVoUqWsl31TGA1vO7IrdZeYys+6kcBI6Qe6jWxRFu0Ao+PtdyUTkwzTUfIcqpnB48bPCxyZzSufpaLg9NOYk0Vu63xQZeG+pamg71LI1vQXMPv8GFwMqdUlOrGVBg4t5FwSIIDLxZW9Yx8hy92bJ66g3Kiy45rJ7Fkq8r2Xmqep71hRx5cFWpx7epbpw8svf69sjOJeNn0jSNgtLefQuAqMfTkk7p9HEs5R4f2HpjYX3a1Vub2Kp5TrZfWIQc/0Rpcz1Bjp5bbG5OOE5+o3hp4LPUxjWVo5Gux1nKFSz8vYQNjg4lBuDQ1HNiiexIXLlRGxTYqe6Vm1Ae7+2v/bLDQhxvEa9R0hhQ3uAxYj/RAFeUfeqiWWfDmpcoXRqawJyFubZD+WH45mZDXGDcQz19QlxwaXaDGQvku3ff7C+IK5XvJgVfD8h863h5sN53WCucYaemrVwdmKOZ5Gx2Q8xxjZ9HbLBjMOjuKIh134J6U0GBxlb5R5sKdqz8xq/dbbBAJTb+zsZaya5aZ9cu48nhSrN0xL05tltwQIFBPbfUswni+I1p5aQMxPHX9NYqjwMkJy/wGFqOZwgGya+GGFtzJ/BVx8N9oQ6qwAUNkl/NnbAictC72YkJVH3mV+CWlgWvFwxuRnqj+7mqIDComZRrC3I8214yEhxoWJWLlsMDTpGfd29wwKHmxeOFggLXpvDv9BW0ppgDJw/5UsfrURBYZN8vjloqniM9i9lca5DLnFvWjt4PUVwsnPsaBWVCkFdNoouaEbe4fStnQdpx1L5FM6QG5RVe/ijYB16Z4GxYyYfVeJEvia4EkrSG+YZr6493EjbXBXLUlyo3LXtxHUpyMqPUej3NcXd856mcpmV6Thb9In1zBc4tULpyDa7l27+gcgcus04/UGrWG+wQ5O5X98LjS3KvBdZqlLzvBiKOR1DZDxNi/fHdkNsocH78uuTmKtY8f3F3gw1PkG+HvP/1EDjQgJuPl3p9z5f2uTbYOwIL94Q7mtnnRb6NzpIZyWKHt14gOeOXUuwTY0cFd7KlaL4tCIzuge+vodPzCRIcBfHC7H9rqSEuTxqT5PTEEThpLrO5sRDHj+1lR6WBOPKFqdQ68ex48bo5+9bMTY5juo+gEnLmG7A2Syj9okQCF1ZC8MT6BidWO78av0GB6svhUCXN/wscdue5uLYcfctnc2ZovqXKWk+bsy/Nbm+rY649XgUu7p41d4Yt2U0xx/EvuDZXBHK0HlSutIS4IPfZ78RNxGX+L9fIfigOgoWfTig4BY6UVxLSulSoCV65bm7AgdJvmaWneRrExTWs7hb05/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"DOC ID" = _t, DATE = _t, AMOUNT = _t, #"STORE ID" = _t, #"STORE NAME" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"DOC ID", Int64.Type}, {"STORE ID", Int64.Type}, {"AMOUNT", Currency.Type}})
in
    #"Changed Type"

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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