Custom average line in line/clustered column chart

[whereismydata]

Hi,

I did a couple of changes in an existing report, basically I moved all transformations to SSAS tabular and created some dimension tables.

(each column represents a product per year, Value is [Value_filtered])

this was working in the old report:

the following measures are used to calculate an custom average line

Value_filtered =

//this measure is used to show only values if all filters are applied (if not all filters are applied, the visuals shows nothing)
IF (
    DISTINCTCOUNT ( 'Import'[Value] ) = 1,
    AVERAGE ( 'Import'[Value] ),
    ""
)

TotalThisYear =

//this measure 'calculates' the value of the product '* total' (which is a weighted total, calculated in another system) and for the //latest year, but only when the product is not 'MA' (because it makes no sense for this product).

//In other words: I would like to apply this value to all products to show the average line

VAR var_max_year =
    MAX ( 'Import'[Year] )
RETURN
    IF (
        SELECTEDVALUE ( 'Import'[Fact] ) = "MA",
        "",
        CALCULATE (
            [Value_filtered],
            'Import'[Year] = var_max_year,
            Product_Dim[Product] = "* Total"
        )
    )

Now, after my cleanup I adapted the formulas like this:

Value_filtered =
IF (
    DISTINCTCOUNT ( Import_new[Value] ) = 1,
    AVERAGE ( Import_new[Value] ),
    ""
)

TotalThisYear =
VAR var_max_year =
    MAX ( Date[Year] )
RETURN
    IF (
        SELECTEDVALUE ( Import_new[Fact] ) = "MA",
        "",
        CALCULATE (
            [Value_filtered],
            Date[Year] = var_max_year,
            Product_Dim_new[Product] = "* Total"
        )
    )

But, all I got is a single dot, no line:

Note: before my cleanup, year was a separate column in the Import table. Now I use year from a dimension table.

Additional information:
there is a one to many connection between the dim tables and import_new, also in the old report, there was a one to many connection between the dim and import table. Filterdirection 'both'

What I tried:

• adding a year column to the import_new table. Didn't help of course
• testing the Total year measure:
  • removed the hole calculate part and set a constant. the line was applied like it should be
  • removed the last part of the calculate (Product_Dim_new[Product] = "* Total"), a line was plotted, but not a straight line, it just followed the peaks of the green columns. Which also makes sense. But again, not what I wanted.

I tried lots of other things, but I can get the result of TotalThisYear (for example lets say 2 000) to be applied to all oder products as well.

Of course I can use a clustered column chart mit the average line, but this is not as flexibel as I want it to be.

can someone help me please?

[Anonymous]

Hi @whereismydata ,

It is a little difficult to find the cause of problem if there is no corresponding data and file. Could you please provide the setting of the visual object and the formula of applied measure/calculated column on it? It would be better if you could provide your sample pbix file(exclude sensitive data).

Best Regards