Forum Discussion

Bubbles's avatar
Bubbles
New Member
1 year ago
Solved

clustered column help

Hi there  I am wondering if some one could help me with the below challange,  I am looking to remove columns, in a clustered column, i have made the x axis categorical and have also tried to fliter...
Need Help
  • v-sdhruv's avatar
    v-sdhruv
    1 year ago

    Hi Bubbles ,

    Categorical data with clustered columns and  sparse data across multiple measures leads to "empty category" spacing issues — Power BI will still allocate space for every combination.

    Use a measure that returns Blank() for unwanted categories.

    Ensure that forecast measures  return absolutely nothing (BLANK) for earlier years.
    Try:

    VAR IsFuture = SELECTEDVALUE('calendar'[FYIndexCAL]) > MAX('calendar'[today_Yr_flag])

    RETURN
    IF(IsFuture,
    Q1Actuals + Q2 + Q3 + Q4,
    BLANK()
    )

    You can even try: Line/column combo chart

    Line charts handle sparse data points gracefully

    • Plot Actuals as columns,

    • Plot Forecasts (2Yr, 3Yr, 5Yr) as lines,

    • Use a single continuous time axis (e.g., year + quarter)

    Hope this helps!
    If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank You!

ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

pls provide some sample data and expected output

  • Bubbles's avatar
    Bubbles
    New Member
    1 year ago

    This is a chart with years on the x axis,   and on the y- axis i have actual data from 20 - 25 and then the 3 coloured columns at the end are sum of estimated quarters based on 2 year avg QoQ Sequentials, 3 year and 5 year.    These columns are the reason why i have the extra spaces, so trying to find work around.   I seperated the actuals and then the forecast in to 2 seperate charts using the fx formula with max of actual data but this doesnot work either as the scale is still off.

    Also when in the same chart if i try and filter by is not blank i am unbale to get all the data points showing.

     

     

     

     

    some of forecast line code.  - 

    FY26(5Yr) =

    Var Q1Actuals = (CALCULATE([Qtr Act], All(quarters), All(years), quarters[fq] =1, years[fy] = 26)) /// getting Q1 actuals

    VAR condition1 = IF(SELECTEDVALUE('calendar'[FYIndexCAL]) > MAX('calendar'[today_Yr_flag]), BLANK(), Q1Actuals) // the above brings Q1 actuals to each year in chart , this line is removing it from history

    //VAR Q1 = CALCULATE([EST1(5Yr)],All(quarters),ALL(years), quarters[fq]=1, years[fy] = SELECTEDVALUE(years[fy]))

    VAR Q2 = CALCULATE([EST1(5Yr)],All(quarters),ALL(years), quarters[fq]=2, years[fy] = SELECTEDVALUE(years[fy]))

    VAR Q3 = CALCULATE([EST2(5Yr)],All(quarters),ALL(years), quarters[fq]=3, years[fy] = SELECTEDVALUE(years[fy]))

    VAR Q4 = CALCULATE([EST3(5Yr)],All(quarters),ALL(years), quarters[fq]=4, years[fy] = SELECTEDVALUE(years[fy]))


    Var output = condition1 + Q2 +Q3 +Q4

    RETURN
    output