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

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.

Reply
JulieB_
Helper I
Helper I

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

 

 



JulieB_
Helper I
Helper I

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors