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.
Hello everyone,
I have a table in my report where I use two measures that are as follows:
measure c = DIVIDE((SUM('Table[Value A])*12),SUM('Table'[Value B]))
and measure to calculate the average of this measure c
average measure c = AVERAGEX ( VALUES ( Calendar[Date].[Month] ), [measure c] )
This is the result of my table:
date | team | value A | value B | measure C | average measure C |
ago/21 | A | 3711,45 | 166193908,1 | 0,02680% | 0,02680% |
set/21 | A | 2549,99 | 170019941 | 0,01800% | 0,01800% |
out/21 | A | 19605,06 | 173440583,5 | 0,13564% | 0,13564% |
nov/21 | A | 24001,52 | 180851849,5 | 0,15926% | 0,15926% |
dez/21 | A | 14640,11 | 189116781,8 | 0,09290% | 0,09290% |
jan/22 | A | 5830,26 | 197560129,6 | 0,03541% | 0,03541% |
fev/22 | A | 16711,02 | 206557220,3 | 0,09708% | 0,09708% |
mar/22 | A | 16551,37 | 203876476,5 | 0,09742% | 0,09742% |
abr/22 | A | 14733,25 | 201803932,3 | 0,08761% | 0,08761% |
mai/22 | A | 17461,04 | 207193959,2 | 0,10113% | 0,10113% |
jun/22 | A | 31595,37 | 218213661,2 | 0,17375% | 0,17375% |
jul/22 | A | 76286,54 | 224224552,4 | 0,40827% | 0,40827% |
TOTAL | 243676,98 | 2339052995 | 0,12501% | 0,11944% |
The result of my average of measure c in this table, using Calendar[Date].[Month] is 0,11944%. The problem is, when I change to Calendar[Date].[Year] my result is 0,11766%. Could someone please explain to me why I'm having this result change and what would be the correct one?
Thank you
@hama21 Well, the reason is that you are taking an average across either 2 rows (Year) or 12 rows (Month). It is also possible that VALUES is returning a blank row and that might also throw some things off. I think that your formula would be much more clear and more flexible if you did not use the auto-time intelligence stuff which has the potential to cause issues as well as use SUMMARIZE or DISTINCT versus VALUES. Seriously, don't use VALUES: https://youtu.be/vOUdKXZpmSc
How do you recommend my formula? I'm watching the video right now. Thanks for the answer @Greg_Deckler
@hama21 I would need a better sense of your source data to be 100% sure. Is what you posted a good example of how your source data looks? But, I would personally try to do something along the lines of:
Average Measure =
VAR __Table =
SUMMARIZE(
ADDCOLUMNS(
'Table',
"__Month",MONTH('Table'[Date])
),
[__Month],
"__Measure",[measure c]
)
RETURN
AVERAGEX(__Table,[__Measure])
A little more verbose because I don't know if you have a Month column already. The advantages of this approach is that you can troubleshoot it by using CONCATENATEX instead of AVERAGEX to inspect your table values within the calculation itself. Also, this method removes any issues that might be caused via table configuration. This form works in a single table data model as well as a star, schema, etc.
@Greg_Deckler It's working but I received different value when using .[month] or .[year]. I want to understand why this is happening because the value must be the same
@hama21 Ah, turns out that it is a measures total problem. I attached a PBIX that demonstrates how to get the same total for both .[Month] and .[Year] below signature.
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |