Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
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):
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.
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.
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 , Try if this measure can work
calculate(averageX(values(Table[Yearmonth]),[Measure]), allselected(Table[Yearmonth]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |