Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
Hi,
I want to find the top 3 stores of the weekly top 3 stores in the selected period (data selection in the slicers).
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!
Solved! Go to Solution.
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
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
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"
User | Count |
---|---|
13 | |
10 | |
8 | |
7 | |
5 |
User | Count |
---|---|
24 | |
16 | |
15 | |
10 | |
7 |