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,
So I have the following table "Table1"). And I want to get the average sales from the two months, which should be: 31 (total january) + 28 (total february)/ 2 = 29,5
I did sum(Table1[Sales])/Calculate(Distinctcount[Table1[month]), Table1[sales]>=0) - and it worked.
But is there another way? I tried with the averagex(summarize..) without success (how does one even mention the name of the newly summarized virtual column), or averagex(values month), sum sales)and this is giving me the total... 59
Thanks
Table1 (sales is always 1 to simplify)
Date | Sales | Month |
january 1 | 1 | january |
january 2 | 1 | january |
..... | 1 | ... |
February 28 | 1 | February |
Solved! Go to Solution.
@campelliann
You can create a measure as :
Sales average per Month =
AVERAGEX(
VALUES('Table1'[Month]),
CALCULATE(SUM('Table1'[Sales]))
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy , you are brilliant :). The values approach was not working, because I was not using calculate around the sum. You need the calculate to "activate" a sort of filter context row by row, right?
Other wise I get always the total sum of the table, thus the average being the total.
Hi @campelliann
If you need SUMMARIZE, you need variable, but performance wise, no need to do it
Just to show you, using GROUPBY, but it is not recommended in this case
avg2 =
VAR T1 = GROUPBY(Table1,Table1[Month],"SALES", SUMX(CURRENTGROUP(),Table1[Sales]))
RETURN
AVERAGEX(T1,[SALES])
simply do it is quicker
avg = SUM(Table1[Sales])/COUNTROWS(VALUES(Table1[Month]))
@campelliann
You can create a measure as :
Sales average per Month =
AVERAGEX(
VALUES('Table1'[Month]),
CALCULATE(SUM('Table1'[Sales]))
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy , you are brilliant :). The values approach was not working, because I was not using calculate around the sum. You need the calculate to "activate" a sort of filter context row by row, right?
Other wise I get always the total sum of the table, thus the average being the total.
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 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |