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 there,
I have daily data of some value of last 5 years. I am struggling since morning to create a measure that calculates average in a way that first it calculates the sum of values by month and then calculates the average value.
sum_by_month = calculate(sum[Value],ALLexcept(table,table['month'])
average_value = averagex(ALLEXCEPT(table,table['RY'],sum_by_month)
However, it does not produce the desired output. I have attached the sample data https://1drv.ms/x/s!AucycxZHFe9TjUYX62xq1jm0mV9x?e=ZRyNP1.
Could any one help me where am I making the mistake?
Solved! Go to Solution.
Hi @Dunner2020,
It always helps if you show the expected result. It avoids misunderstandings. Try this measure in a card visual:
Measure =
VAR sumT_ =
ADDCOLUMNS (
SUMMARIZE ( Table1, Table1[RY], Table1[month] ),
"SumByMonth", CALCULATE ( SUM ( Table1[Value] ) )
)
RETURN
AVERAGEX ( sumT_, [SumByMonth] )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Dunner2020,
It always helps if you show the expected result. It avoids misunderstandings. Try this measure in a card visual:
Measure =
VAR sumT_ =
ADDCOLUMNS (
SUMMARIZE ( Table1, Table1[RY], Table1[month] ),
"SumByMonth", CALCULATE ( SUM ( Table1[Value] ) )
)
RETURN
AVERAGEX ( sumT_, [SumByMonth] )
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |