clustered column help

[Bubbles]

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 non blanks but it wont work.   see sample of my data below.

[v-sdhruv]

Hi @Bubbles ,
Just wanted to check if you had the opportunity to review the suggestion provided?
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

[v-sdhruv]

Hi @Bubbles ,
Just wanted to check if you had the opportunity to review the suggestions provided?
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

[v-sdhruv]

Hi @Bubbles ,
Just wanted to check if you had the opportunity to review the suggestions provided?
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

[v-sdhruv · Accepted solution]

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!

[v-sdhruv]

Hi @Bubbles ,
Thanks for reaching to Microsoft Community Forum.

You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community

How to upload PBI in Community
Hope this helps!

[ryan_mayu]

pls provide some sample data and expected output

[Bubbles]

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

[pankajnamekar25]

Hello @Bubbles 

Could you share sample pbix 

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.