Forum Discussion

hnguyen76's avatar
hnguyen76
Resolver II
6 years ago
Solved

Clustered Column Chart Statically Set Colors

Good morning, All. I am using a clustered column chart where values are driven by a Quarter-Year slicer. My visual setup is as followed: When a user selects a different quarter-year, I ...
  • Anonymous's avatar
    Anonymous
    6 years ago

    hnguyen76  - To clarify, is this correct?: Consistency in this case means that there will always be 3 columns: first column will be dark blue, second medium, and third light. 

     

    The problem with this is that you want the color for a particular quarter to change depending on its position in the chart.

     

    One way to accomplish this is by using static categories in the Legend.

    1. Create a Date Table that contains relative quarters (like the Date table in the attached pbix).

    2. Create a Parameter table with the list of quarters and their relative quarters from above date table.

     

     

     

    Parameters = 
    var a = SELECTCOLUMNS('Date',"Quarter Year", 'Date'[Quarter Year], "Relative Quarter",'Date'[Relative Quarter])
    var b = DATATABLE(
        "Selected Quarter Description", STRING, 
        "Selected Quarter Order", INTEGER, 
        {{"Previous Year", 1},{"Previous Quarter", 2},{"Selected Quarter", 3}}
    )
    return CROSSJOIN(a,b)

     

     

     

    3. On the Model Pane, set the Sort By value for the various Quarter values.

    4. Create a relationship between your fact table and the new Date table.

    5. Create a Measure which gets the value for the relevant quarters.

     

     

     

    Value By Relevant Quarters = 
    var _ThisQuarter = SELECTEDVALUE(Parameters[Relative Quarter])
    var _LastQuarter = _ThisQuarter - 1
    var _LastYearQuarter = _ThisQuarter - 4
    return
    SWITCH(
        MAX('Parameters'[Selected Quarter Order]),
        1, CALCULATE(SUM(DummyData[Value]), 'Date'[Relative Quarter] = _LastYearQuarter), 
        2, CALCULATE(SUM(DummyData[Value]), 'Date'[Relative Quarter] = _LastQuarter),
        3, CALCULATE(SUM(DummyData[Value]), 'Date'[Relative Quarter] = _ThisQuarter)
    )

     

     

     

    6. Create a slicer from the Parameters[Quarter Year].

    7. Add a Column Chart, with Parameters[Select Quarter Description] in the Legend, and [Value by Relative Quarters] for the values.

     

    Please see attached PBIX which demonstrates the logic.

     

    I hope this helps. If it does, please Mark as a solution.
    I also appreciate Kudos.