Group­ing Data

  • When mak­ing charts/graphs, you can sort your data via groups – but groups are sta­t­ic and will have to be man­u­al­ly updat­ed if you add more data to your visu­al (For exam­ple, num­ber of peo­ple in age ranges). Instead, use cal­cu­lat­ed columns and ref­er­ence those as your axis instead. Sam­ple code:

IF([Age]<31, "0-30",
     IF(31<= [Age] && [Age] < 61, "31-60",
          IF(61 <= [Age] && [Age] < 91, "61-90",
               IF(91 <= [Age] && [Age] < 121, "91-120", "Over 120"))))

  • Syn­tax: IF([condition], “[label]”, “[else label]”)
  • Above uses nest­ed IF’s
  • * Note: Can­not group mea­sures for cus­tomized visu­al dis­play. Group­ing this way only works for indi­vid­ual columns, to the extent of my under­stand­ing

Cus­tom Sort Axis Labels

  • Pow­er BI does not have an easy way to cus­tom sort axis labels either as of the writ­ing of this doc­u­ment. Cur­rent­ly the quick and dirty way around it is to use peri­ods at the begin­ning of axis titles.
    • Exam­ple: months of the year
      • Jan­u­ary, Feb­ru­ary, March, and April would be auto sort­ed as April, Feb­ru­ary, Jan­u­ary, March. To solve this, the workaround is to name them as such: “…Jan­u­ary”, “..Feb­ru­ary”,  “.March”, “April”
    • The clean­er method is to make a new col­umn in your query, and treat it as an index for your axis labels. Con­tin­u­ing with the months exam­ple:

1. Top Rib­bon > Home > Edit Queries > Select your table/Query on the left
2. Right click on and dupli­cate your Month col­umn
3. On the dupli­cat­ed col­umn (By default, “Month – Copy”. I like to rename it as “Month – Index”), right click and choose “Replace Val­ues”
4. Val­ue To Find: Jan­u­ary
— Replace With: 1
— OK
5. Repeat with all months, until the “Month – Copy” col­umn con­tains only num­bers 1 – 12.
6. Change data type to Whole Num­bers
7. Close & Apply
8. Under Fields, go to your table and click on the Month col­umn

9. Go to Col­umn Tools in the rib­bon, then “Sort by Col­umn”

  • If the but­ton is greyed out, make sure you’ve clicked on the Month col­umn

10. Select “Month – Index” (your dupli­cat­ed col­umn).
11. Your visu­al should now dis­play the months in the cor­rect order, with­out hav­ing to use peri­ods
— Thor­ough expla­na­tion here: https://www.c‑sharpcorner.com/article/sort-by-month-name-in-power-bi/ or archived through the way­back­ma­chine: https://web.archive.org/web/20190723215646/https://www.c‑sharpcorner.com/article/sort-by-month-name-in-power-bi/