Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
12 | |
9 | |
9 | |
9 |
User | Count |
---|---|
19 | |
14 | |
14 | |
13 | |
12 |