The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have a table where i only have about 1 months worth of data at any given time. I would like to take the average of these values and have the average populate for Jan 2023 to current date aka where my actual data ends.
I calculated the average using the measure below but as you can see in my table it still only populates for the dates that I currently have. I would like the 5.38 to show for Jan 1st, Jan 2nd, Jan 3rd and so on until we hit May 15th where it will populate the actual value. Any thoughts?
Thank you!
Solved! Go to Solution.
I expected that. It's ALLSELECTED that is doing the stange things 😅
Please try
Measure2 =
VAR AverageValue =
AVERAGEX ( ALLSELECTED ( 'Calendar Table'[Date] ), [Manual Appts Calc] )
RETURN
SUMX (
VALUES ( 'Calendar Table'[Date] ),
COALESCE ( [Manual Appts Calc], AverageValue )
)
Thanks that worked! How can i now get the sum of these numbers? When its at the day level it populates the average for each day but if i were to change this at a month level its still just taking the average for that day and not summing all of the days in that month.
Please try
Measure2 =
SUMX (
VALUES ( 'Calendar Table'[Date] ),
COALESCE (
[Manual Appts Calc],
AVERAGEX ( ALLSELECTED ( 'Calendar Table'[Date] ), [Manual Appts Calc] )
)
)
Thank you! So looks like it summed correctly, however now the values (averages) are missing on the days where they were populating previously.
Measure 3 is the original measure you provided and test manual inbounds is the new one trying to sum all of these values together. My goal is to have values populate for every date and the total be the sum of all of these together.
I expected that. It's ALLSELECTED that is doing the stange things 😅
Please try
Measure2 =
VAR AverageValue =
AVERAGEX ( ALLSELECTED ( 'Calendar Table'[Date] ), [Manual Appts Calc] )
RETURN
SUMX (
VALUES ( 'Calendar Table'[Date] ),
COALESCE ( [Manual Appts Calc], AverageValue )
)
Yay, that worked!! thank you so much!
Hi @Bryanna
please try
Measure2 =
COALESCE (
[Manual Appts Calc],
AVERAGEX ( ALLSELECTED ( 'Calendar Table'[Date] ), [Manual Appts Calc] )
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
32 | |
21 | |
16 | |
15 |
User | Count |
---|---|
117 | |
34 | |
30 | |
24 | |
21 |