Forum Discussion

FrankvanDorp's avatar
FrankvanDorp
Helper I
3 years ago
Solved

Sort clustered column chart based on slicer

Relative beginner in PowerBI here. I have this table, showing the last month's profits for a company in several different countries. The next column shows the change of this figure relative to a prev...
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    FrankvanDorp,

     

    Try this solution.

     

    1. Create table SlicerPeriod (no relationships):

     

     

    2. Create measures:

     

    Change = SUM ( FactTable[Change] )
    Sort Order = 
VAR vPeriodSelection =
    SELECTEDVALUE ( SlicerPeriod[Period] )
VAR vCurrentCountry =
    SELECTEDVALUE ( FactTable[Country] )
VAR vTableAmount =
    ADDCOLUMNS (
        ALLSELECTED ( FactTable[Country] ),
        "@Amount", CALCULATE ( [Change], FactTable[Period] = vPeriodSelection )
    )
VAR vTableRank =
    ADDCOLUMNS ( vTableAmount, "@Rank", RANKX ( vTableAmount, [@Amount],, ASC ) )
VAR vResult =
    MAXX ( FILTER ( vTableRank, FactTable[Country] = vCurrentCountry ), [@Rank] )
RETURN
    vResult

     

    3. Create slicer using SlicerPeriod[Period].

     

    4. Create matrix and add the measure [Sort Order] as a Tooltip (sort Ascending):

     

     

    5. Add remaining fields to matrix. Legend uses FactTable[Period].

     

     

    6. Result:

     

    ---

     

DataInsights's avatar
DataInsights
Super User
3 years ago

FrankvanDorp,

 

Try this solution.

 

1. Create table SlicerPeriod (no relationships):

 

 

2. Create measures:

 

Change = SUM ( FactTable[Change] )
Sort Order = 
VAR vPeriodSelection =
    SELECTEDVALUE ( SlicerPeriod[Period] )
VAR vCurrentCountry =
    SELECTEDVALUE ( FactTable[Country] )
VAR vTableAmount =
    ADDCOLUMNS (
        ALLSELECTED ( FactTable[Country] ),
        "@Amount", CALCULATE ( [Change], FactTable[Period] = vPeriodSelection )
    )
VAR vTableRank =
    ADDCOLUMNS ( vTableAmount, "@Rank", RANKX ( vTableAmount, [@Amount],, ASC ) )
VAR vResult =
    MAXX ( FILTER ( vTableRank, FactTable[Country] = vCurrentCountry ), [@Rank] )
RETURN
    vResult

 

3. Create slicer using SlicerPeriod[Period].

 

4. Create matrix and add the measure [Sort Order] as a Tooltip (sort Ascending):

 

 

5. Add remaining fields to matrix. Legend uses FactTable[Period].

 

 

6. Result:

 

---

 

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    Correction: "Clustered column chart", not matrix. 🙂

    • FrankvanDorp's avatar
      FrankvanDorp
      Helper I
      3 years ago

      For some reason, after adding all that I do get a sorted chart, but it doesn't show both 'Last Month' and 'Same Month Last Year', only the one I selected.

      When I deselect, I get a graph with both again, though unsorted:

      (btw the new measure Change I named 'Change2' because it won't allow me to name it Change as there is already a column with that name).

      Is there maybe some step I missed?

       

      • DataInsights's avatar
        DataInsights
        Super User
        3 years ago

        FrankvanDorp,

         

        Note step 3: Create slicer using SlicerPeriod[Period]. It appears your slicer uses the Period field from your data table, causing it to filter the data. The table SlicerPeriod doesn't filter the visual; it's merely a way to capture the user's selection.

         

        I use a measures table to avoid naming conflicts like the one you mentioned, and to organize measures so they aren't scattered throughout tables. This is just a personal preference, and won't impact results.