cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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!

7 REPLIES 7
Community Support

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
Not applicable

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):

Community Support

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 ] ) ),
)
),
[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
Not applicable

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.

Community Support

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
Not applicable

@amitchandak unfortunately not 😞

Super User

@Anonymous , Try if this measure can work

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.