Forum Discussion
Helper IIIDisappearing data on combo chart (line + bar chart)
Hi all,
I'm facing an issue, I'm using a Line and clustered column chart, but what's happening is, when I add an average line which shows correctly calculated, my december month simply disappears.
You can find the measures I'm using below.
Have anyone faced an issue like this?
With average line:
Without average line:
I couldn't figure out why.
Measures:
_Sales Trend Delta % Avg (Year) =
VAR Yr = INT ( MAX ( dCalendar[YearMonthKey] ) / 100 )
RETURN
CALCULATE (
AVERAGEX ( VALUES ( dCalendar[YearMonthKey] ), [_Sales Trend Delta %] ),
REMOVEFILTERS ( dCalendar ),
FILTER ( ALL ( dCalendar[YearMonthKey] ), INT ( dCalendar[YearMonthKey] / 100 ) = Yr )
)
_Sales Trend Delta % = DIVIDE ( [_Sales Trend CY] - [_Sales Trend PY], [_Sales Trend PY] )
_Sales Monthly CY =
VAR StartD = [_Sales Start Value]
VAR EndD = [_Last Full Month End]
VAR StartKey = YEAR ( StartD ) * 100 + MONTH ( StartD )
VAR EndKey = YEAR ( EndD ) * 100 + MONTH ( EndD )
VAR CurrType = SELECTEDVALUE ( Sales[Currency Type], 30 )
RETURN
CALCULATE (
AVERAGE ( Sales[Sales] ),
KEEPFILTERS (
dCalendar[YearMonthKey] >= StartKey
&& dCalendar[YearMonthKey] <= EndKey
),
Sales[Currency Type] = CurrType
)
_Sales Monthly PY =
VAR StartD = [_Sales Start Value]
VAR EndD = [_Last Full Month End]
VAR StartKey = YEAR ( StartD ) * 100 + MONTH ( StartD )
VAR EndKey = YEAR ( EndD ) * 100 + MONTH ( EndD )
VAR CurrType = SELECTEDVALUE ( Sales[Currency Type], 30 )
VAR PYKeys =
SELECTCOLUMNS (
FILTER (
VALUES ( dCalendar[YearMonthKey] ),
dCalendar[YearMonthKey] >= StartKey
&& dCalendar[YearMonthKey] <= EndKey
),
"@Key", dCalendar[YearMonthKey] - 100
)
RETURN
CALCULATE (
AVERAGE ( Sales[Sales] ),
REMOVEFILTERS ( dCalendar ),
TREATAS ( PYKeys, dCalendar[YearMonthKey] ),
Sales[Currency Type] = CurrType
)
_Sales Start Value =
IF (
ISFILTERED ( 'Sales Start Date'[Date] ),
MIN ( 'Sales Start Date'[Date] ) -1,
DATE ( 2022, 1, 1 )
)
_Last Full Month End = EOMONTH ( [_As Of Date Value], -1 )
_As Of Date Value =
IF (
ISFILTERED ( 'As Of Date'[Date] ),
MIN ( 'As Of Date'[Date] ),
CALCULATE ( MAX ( 'As Of Date'[Date] ), REMOVEFILTERS ( 'As Of Date' ) )
)
TABLE: As Of Date = CALENDAR ( DATE ( 2020, 1, 1 ), TODAY () )
Hello everyone, thanks all for sharing and replying to this topic.
I still didn't figure out why, but what actually solve the issue was disabling the shared axis. I don't know why but when this was enabled, December decided to not show up, and it happened with this month only.
Anyway, I really appreciate all the answers here in this thread.
7 Replies
lcfariaHello everyone, thanks all for sharing and replying to this topic.
I still didn't figure out why, but what actually solve the issue was disabling the shared axis. I don't know why but when this was enabled, December decided to not show up, and it happened with this month only.
Anyway, I really appreciate all the answers here in this thread.
- ShahRukhSameer
Solution Sage
Hi lcfaria,
I’ve seen this kind of issue with combo charts before. When you add an average line, the calculation changes the filter context and sometimes a month gets excluded. That’s why December disappears in your case.
Here are a few things you can try:
Check that your date table is marked as a proper Date table and that the relationship to your fact table is active.
The part of your measure with REMOVEFILTERS(dCalendar) and the custom filter logic can override the month context. Try removing REMOVEFILTERS or replacing it with KEEPFILTERS to see if December comes back.
Test with a simpler version of the measure, for example: Sales Trend Delta % Avg (Year) = AVERAGEX(VALUES(dCalendar[YearMonthKey]), [_Sales Trend Delta %]) If that shows all months correctly, then the extra filter logic is the cause.
Also check if December has blank values in your data. Sometimes blanks get dropped when the average line is calculated.
The chart itself isn’t broken. It’s the way the measure is handling filters. Simplify the logic first, confirm December appears, then add back the year calculation step by step until you see where it’s being excluded.
ryan_mayuSuper User
pls try if this can help.
_Sales Trend Delta % Avg (Year) =
VAR CurrentYear =
SELECTEDVALUE ( dCalendar[Year] )
VAR MonthsForYear =
CALCULATETABLE (
VALUES ( dCalendar[YearMonthKey] ),
REMOVEFILTERS (
dCalendar[MonthName],
dCalendar[MonthNumber],
dCalendar[YearMonthKey]
),
dCalendar[Year] = CurrentYear
)
RETURN
IF (
NOT ISBLANK ( CurrentYear ),
AVERAGEX (
MonthsForYear,
CALCULATE ( [_Sales Trend Delta %] )
)
)
it this does not work, pls provide some sample data and expected output.
- danextian
I can't replicate this. If you could share a sanitized copy of your pbix then it would be easier for us to figure out what's wrong
- Murtaza_Ghafoor
Hi lcfaria,
This is more likely an error in the measure so I would recommend following debugging order, first of all use table view to see all values which will help which measure is returning balnk values.
- Put YearMonthKey in a table.
- Add measure _Sales Monthly CY.
- Add measure _Sales Monthly PY.
- Add measure_Sales Trend Delta %.
- Add measure _Sales Trend Delta % Avg (Year).
Find exactly which measure becomes BLANK() for December.If December has values but disappears only after adding the average line, check the X-axis Categorical/Continuous setting and Show items with no data.
Replace the average measure with the simplified version measure. Such as for instead using VAR Yr = INT ( MAX ( dCalendar[YearMonthKey] ) / 100 )
Use VAR Yr = SELECTEDVALUE ( dCalendar[Year] ), in your measure(_Sales Trend Delta % Avg (Year)) will definitely fix the problems.
Most importantly: don't try to fix this by forcing December to 0 until you determine which measure is blank.
Zero(0) and a BLANK() have very different meanings in Power BI, especially in combo charts.
If this helps, ✓ Mark as Kudos | Help Others
v-achippaCommunity Support
Hi lcfaria,
Thank you for reaching out to Microsoft Fabric Community.
Thank you ShahRukhSameer, ryan_mayu, danextian and Murtaza_Ghafoor for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
