Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello,
So the main problem is that I would like to show the average numbers per Week numbers. But I don't know why but it sums up all the averages, and not just average the averages.
My current DAX formula:
Average =
AVERAGEX(
FILTER('Code',Code[Code_name] = "Failure"),
CALCULATE([Gross Time])
)
Where Gross Time is another Measure.
In the picture you can see that calculating the average by ID works, but if you collapse the matrix it shows weird numbers per week number.
Please help
Solved! Go to Solution.
@Anonymous do you mean this?
| ID | Sales |
|----|-------|
| A | 10 |
| A | 20 |
| B | 30 |
| B | 40 |
| B | 50 |
| B | 60 |
| C | 70 |
| D | 80 |
_customAverage =
VAR _1 =
ADDCOLUMNS (
'Table',
"average",
VAR a = 'Table'[ID]
RETURN
AVERAGEX ( FILTER ( 'Table', 'Table'[ID] = a ), [_salesSum] )
)
VAR _2 =
ADDCOLUMNS (
_1,
"rank", RANKX ( FILTER ( _1, [ID] = EARLIER ( [ID] ) ), [_salesSum],, ASC )
)
VAR _3 =
ADDCOLUMNS ( _2, "newAvg", IF ( [rank] = 1, [average] ) )
RETURN
IF (
HASONEVALUE ( 'Table'[ID] ),
AVERAGEX ( _1, [average] ),
AVERAGEX ( _3, [newAvg] )
)
pbix is attached
@Anonymous do you mean this?
| ID | Sales |
|----|-------|
| A | 10 |
| A | 20 |
| B | 30 |
| B | 40 |
| B | 50 |
| B | 60 |
| C | 70 |
| D | 80 |
_customAverage =
VAR _1 =
ADDCOLUMNS (
'Table',
"average",
VAR a = 'Table'[ID]
RETURN
AVERAGEX ( FILTER ( 'Table', 'Table'[ID] = a ), [_salesSum] )
)
VAR _2 =
ADDCOLUMNS (
_1,
"rank", RANKX ( FILTER ( _1, [ID] = EARLIER ( [ID] ) ), [_salesSum],, ASC )
)
VAR _3 =
ADDCOLUMNS ( _2, "newAvg", IF ( [rank] = 1, [average] ) )
RETURN
IF (
HASONEVALUE ( 'Table'[ID] ),
AVERAGEX ( _1, [average] ),
AVERAGEX ( _3, [newAvg] )
)
pbix is attached
@Anonymous did you have a chance to look into this?
Hi @Anonymous
Is the value in your matrix measure or column?
Supposing it's a column, here is an example,
Measure = AVERAGEX('Table','Table'[Column])
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
The problem is that it's a measure. With column it would work perfect. But it's a measure.
Hi @Anonymous
Thanks for your reply and patience, I noticed that I missed your initial description.
If they are measures, it's also achievable to change the value in Row subtotals.
Besides, I need something like bellow to write specified measure which meets your needs,
(1) some sample data in text, the values of all the fields involved in the measure [Average] & [Gross Time]).
and the DAX expression of [Gross Time].
(2) or a sample file.
Thanks.
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
so based on the picture, what's the expected result? Thanks
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
It should be
2021.45 2.935
So the Average column should "average the averages" not sum it.
@Anonymous ,
If gross time is Avg, simply do
Average =
CALCULATE([Gross Time], FILTER('Code',Code[Code_name] = "Failure"))
or try like
Average =
calculate( AVERAGEX(Values('Code'[Year Week No])
,
[Gross Time]
) ,FILTER('Code',Code[Code_name] = "Failure") )
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
85 | |
65 | |
51 | |
45 |
User | Count |
---|---|
217 | |
88 | |
81 | |
65 | |
56 |