Average line not to be affected by x-axis values/categories

[Anonymous]

Hi everyone!

I have a bar chart with Year-Month on the x-axis and my y-axis values = average tasks per project.

This is an example of my data:

yearMonth	Project ID	Total Tasks	Measure
20214	71248	11	11
20214	71360	10	10
20214	72567	2	2
20215	71240	1	1
20215	71254	11	11
20215	71990	8	8
20215	72322	1	1
20215	72342	1	1
20215	72356	1	1
20215	72357	1	1
20215	72910	1	1
20215	72957	1	1
20216	71281	1	1
20216	72188	5	5
20216	72225	13	13
20216	72351	4	4
20216	72352	6	6
20216	72969	3	3

So I have the bar chart that represents "Total tasks per project over Time". Now I would like to add a horisontal line representing Average tasks per project over ALL time, but everything I tried (ALL, ALLEXCEPT, FILTER etc) ended up looking like this:

I need the orange line to not be affected by the month, but rather Total tasks divided by Total projects divided by 3 months.

The average line made available by the analytics pane gives me the correct value (5.33 in this case), but I need to visualise that value on a Card, and I'm doing something wrong...

Help would be much appreciated!

[v-janeyg-msft]

Hi, @Anonymous 

How do you get 8, 3, 5 in your visual? Do you choose a specific project? I don't understand your logic very well. Can you explain more information and details? So we can help you soon.

Best Regards

Janey Guo

[Anonymous]

Hi @v-janeyg-msft I simply added the "total tasks" field to the visual's column values and changed the summarization to Average (8, 5 and 3 were rounded up):

[v-janeyg-msft]

Hi, @Anonymous 

There is no context in the card visual, so you need to create a measure using summarize table.

Like this:

Measure =
AVERAGEX (
    SUMMARIZE (
        ALL ( 'Table' ),
        [yearMonth ],
        "a",
            AVERAGEX (
                FILTER ( ALL ( 'Table' ), [yearMonth ] = SELECTEDVALUE ( 'Table'[yearMonth ] ) ),
                [Total Tasks ]
            )
    ),
    [a]
)

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for your response @v-janeyg-msft

The measure you provided doesn't change when I change my date filter?

FYI I have a calender table that filters this table by Project Completed date.

[v-janeyg-msft]

Hi, @Anonymous 

Try to delete all or use allselected instead of all in the formula.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@amitchandak unfortunately not 😞

[amitchandak]

@Anonymous , Try if this measure can work

calculate(averageX(values(Table[Yearmonth]),[Measure]), allselected(Table[Yearmonth]))