Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have this small issue that I cannot solve.
I tried reading but unable to achieve what I need to do.
I have this measure
CALCULATE(
[Total Carico Qty],
FILTER(
ALL('Date Table'),
'Date Table'[Date] <= MAX('Date Table'[Date])
)
) -
CALCULATE(
[Total Scarico Qty],
FILTER(
ALL('Date Table'),
'Date Table'[Date] <= MAX('Date Table'[Date])
)
)
Now there is another field on my table that I want to get the average value.
But I need to filter it where the reult of the above measure is not 0.
The value is displayed on the card visual as per end user's request.
Right now, the average showing is the average of all including the 0 result form above measure.
Any hints on how to handle this?
Thanks!
Product | Measure | For Average |
DC07005 | 0 | 0.85 |
DD04051 | 0 | 0.85 |
DC04310 | 0 | 0.86 |
DC07006 | 0 | 0.86 |
DD01831 | 0 | 0.86 |
DA07845 | 0 | 0.87 |
DA07850 | 0 | 0.87 |
DC04222 | 0 | 0.87 |
DC04318 | 0 | 0.87 |
DC05662 | 0 | 0.87 |
DC05669 | 0 | 0.87 |
DC05673 | 0 | 0.87 |
DC06910 | 0 | 0.87 |
DC07687 | 0 | 0.87 |
DC07689 | 0 | 0.87 |
DC07697 | 0 | 0.87 |
DC08360 | 0 | 0.87 |
DC08956 | 0 | 0.87 |
DC08957 | 0 | 0.87 |
DD04062 | 0 | 0.87 |
DE09293 | 0 | 0.89 |
DE09398 | 0 | 0.89 |
DF09038 | 0 | 0.9 |
DG02770 | 0 | 0.9 |
DA04391 | 0 | 0.91 |
DF09084 | 0 | 0.92 |
DF09407 | 0 | 0.92 |
DF09452 | 0 | 0.92 |
DG05332 | 0 | 0.93 |
DG05638 | 0 | 0.93 |
DG06038 | 0 | 0.93 |
DG07520 | 3890 | 0.93 |
DG07555 | 3320 | 0.93 |
DG07556 | 3750 | 0.93 |
DG07656 | 4540 | 0.93 |
DA04915 | 0 | 0.94 |
DA04917 | 0 | 0.94 |
DA04918 | 0 | 0.94 |
DF00336 | 0 | 0.94 |
DF05198 | 0 | 0.94 |
DF05891 | 0 | 0.94 |
DG01058 | 0 | 0.94 |
DG02776 | 0 | 0.94 |
DG04248 | 0 | 0.94 |
DG04249 | 0 | 0.94 |
DG04250 | 0 | 0.94 |
DG04527 | 0 | 0.94 |
DG05037 | 0 | 0.94 |
DG05038 | 0 | 0.94 |
DG05331 | 0 | 0.94 |
DG05639 | 0 | 0.94 |
DG06037 | 0 | 0.94 |
DG06039 | 0 | 0.94 |
DG07264 | 4220 | 0.94 |
DG07265 | 4120 | 0.94 |
DG07267 | 4290 | 0.94 |
DG07269 | 4390 | 0.94 |
DG07271 | 3790 | 0.94 |
DG07274 | 3530 | 0.94 |
DG07522 | 4000 | 0.94 |
DG07523 | 4480 | 0.94 |
DG07554 | 3760 | 0.94 |
DG07557 | 3920 | 0.94 |
DG07558 | 4220 | 0.94 |
DG07560 | 4740 | 0.94 |
DG07657 | 4090 | 0.94 |
DA04395 | 0 | 0.95 |
DA04921 | 0 | 0.95 |
DA04922 | 0 | 0.95 |
DG05036 | 0 | 0.95 |
DG06036 | 0 | 0.95 |
DG06040 | 0 | 0.95 |
DG07266 | 3900 | 0.95 |
DG07268 | 3930 | 0.95 |
DG07270 | 4000 | 0.95 |
DG07272 | 4370 | 0.95 |
DG07518 | 3610 | 0.95 |
DG07521 | 3930 | 0.95 |
DG07653 | 4560 | 0.95 |
DG07655 | 4420 | 0.95 |
DB06130 | 0 | 0.96 |
DB07338 | 0 | 0.96 |
DB07339 | 0 | 0.96 |
DF07394 | 0 | 0.96 |
DF08477 | 0 | 0.96 |
DF08991 | 0 | 0.96 |
DF08992 | 0 | 0.96 |
DF08994 | 0 | 0.96 |
DB06133 | 0 | 0.97 |
DB06135 | 0 | 0.97 |
DB07271 | 0 | 0.97 |
DB07272 | 0 | 0.97 |
DB07273 | 0 | 0.97 |
DB07275 | 0 | 0.97 |
DB07290 | 0 | 0.97 |
DB07295 | 0 | 0.97 |
DB07334 | 0 | 0.97 |
DF05878 | 0 | 0.97 |
DF07025 | 0 | 0.97 |
DF07026 | 0 | 0.97 |
DF08478 | 0 | 0.97 |
DF08995 | 0 | 0.97 |
DF09085 | 0 | 0.97 |
DB06132 | 0 | 0.98 |
DB07291 | 0 | 0.98 |
DB07292 | 0 | 0.98 |
DB07294 | 0 | 0.98 |
DB07333 | 0 | 0.98 |
DB07337 | 0 | 0.98 |
DB07342 | 0 | 0.98 |
DF07027 | 0 | 0.98 |
DF07350 | 0 | 0.98 |
DF07395 | 0 | 0.98 |
DF06894 | 0 | 0.99 |
DF06895 | 0 | 0.99 |
DF07351 | 0 | 0.99 |
DB06131 | 0 | 1 |
DB07276 | 0 | 1 |
DB07289 | 0 | 1 |
DB07296 | 0 | 1 |
DB07335 | 0 | 1 |
DB07336 | 0 | 1 |
DB07340 | 0 | 1 |
DB07341 | 0 | 1 |
DF06896 | 0 | 1 |
Solved! Go to Solution.
The solution from this forum solved my issue.
https://community.powerbi.com/t5/Desktop/Filter-a-Measure-with-another-Measure/td-p/1380905
The solution from this forum solved my issue.
https://community.powerbi.com/t5/Desktop/Filter-a-Measure-with-another-Measure/td-p/1380905
Hi @mussaenda,
Try measure as:
AVG=
AVERAGEX(
FILTER(
ALL('Date Table'),
'Date Table'[Date] <= MAX('Date Table'[Date])
),
CALCULATE(
[average],
filter(
table,
measure=0
)
)
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
Hi @mussaenda,
It is the column in the table that need to be averaged.
If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
it requires an aggregation for [average]