Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi everyone, hope someone can help me.
Context
I need to monitor the consumptions of a certain substance in a certain equipment per month. The monitoring is for the last 3 months (Consumptions table), the current month and the next 3 months (future consumptions table). In addition, I need to calculate the average and the standard deviation.
To calculate the average and standard deviation, the calculation must be done on the months that have value, that is, the calculation must NOT consider the nulls, for that, I manually calculated the average and standard deviation measures.
*******************
**********************
*********************************************
This is the resulting table
The calculation is correct and works, but I have hundreds of thousands of data, so I have performance issues when loading the visual.
My question is: Is there a different approach I can use to solve my problem? Is there another way to calculate the average and standard deviation using DAX formulas (AVERAGE, STDEV.S with measures as arguments)?
I attach a file with data as an example
NOTES: I'm using DirectQuery (Power BI dataset)
I would really appreciate your help
Solved! Go to Solution.
You should be able to do this with AVERAGEX and STDEVX.S. These functions ignore blanks but include zeroes.
This might work:
AVERAGEX (
{
[Month -2],
[Month -1],
[Current Month],
[Month +1],
[Month +2],
[Month +3]
},
[Value]
)
but you probably don't need separate measures for each month offset when something more like this should work:
AVERAGEX (
GENERATESERIES ( -2, 3 ),
CALCULATE ( [Measure], DATEADD ( 'Date'[Date], [Value], MONTH ) )
)
You could switch between them.
AVERAGEX (
GENERATESERIES ( -2, 3 ),
VAR _i = [Value]
RETURN
IF (
_i <= 0,
CALCULATE ( [SumCurrent], DATEADD ( 'Date'[Date], _i, MONTH ) ),
CALCULATE ( [SumFuture], DATEADD ( 'Date'[Date], _i, MONTH ) )
)
)
You should be able to do this with AVERAGEX and STDEVX.S. These functions ignore blanks but include zeroes.
This might work:
AVERAGEX (
{
[Month -2],
[Month -1],
[Current Month],
[Month +1],
[Month +2],
[Month +3]
},
[Value]
)
but you probably don't need separate measures for each month offset when something more like this should work:
AVERAGEX (
GENERATESERIES ( -2, 3 ),
CALCULATE ( [Measure], DATEADD ( 'Date'[Date], [Value], MONTH ) )
)
Hi @AlexisOlson
Thank you so much for your time and solution!!
Your suggestion to use AVERAGEX/STDEVX.S worked perfectly. I used the example you gave me and that was just what I needed.
Before closing my question, you mentioned that it is probably not necessary to separate the measures for each month. I tried to adapt the example code you provided, but I am not getting the calculation right. I think it is not possible because the months -2, -1 and 0 are from the Consumption table and the months +1, +2 and +3 are from the Future Consumptions table. I don't know how to adapt this code so that the GENERATESERIES and CALCULATE consider both tables. I'm attaching an image of how the measures of each month are calculated. If you have an idea how to improve my calculations, again, I would appreciate it, because I actually need to calculate -6 months and +12 months.
Thanks again
You could switch between them.
AVERAGEX (
GENERATESERIES ( -2, 3 ),
VAR _i = [Value]
RETURN
IF (
_i <= 0,
CALCULATE ( [SumCurrent], DATEADD ( 'Date'[Date], _i, MONTH ) ),
CALCULATE ( [SumFuture], DATEADD ( 'Date'[Date], _i, MONTH ) )
)
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
79 | |
63 | |
61 | |
59 |
User | Count |
---|---|
166 | |
114 | |
99 | |
73 | |
65 |