Forum Discussion
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 non blanks but it wont work. see sample of my data below.
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 chartLine 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!-
8 Replies
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!Hello Bubbles
Could you share sample pbix
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
pls provide some sample data and expected output
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 actualsVAR 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 +Q4RETURNoutput
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 chartLine 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!-
