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

Anonymous — Tue, 08 Feb 2022 08:05:40 GMT

Hi,

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

First I need the top 3 stores per thursday week.
Calculate, stores that have the most stores claims (distinctcount document id)
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!

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

Anonymous — Tue, 08 Feb 2022 08:41:42 GMT

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"

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

Anonymous — Wed, 09 Feb 2022 07:48:49 GMT

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.

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.

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

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

Hope this helps

topic Count number of times store is in TOPN - Calculate a top 3 of a top 3 in DAX Commands and Tips

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

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

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