Measure filter on Card Visual

[mussaenda]

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

[mussaenda · Accepted solution]

The solution from this forum solved my issue.

https://community.powerbi.com/t5/Desktop/Filter-a-Measure-with-another-Measure/td-p/1380905 

[v-xulin-mstf]

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!

[mussaenda]

Hi @v-xulin-mstf ,

Thanks for your help.

The [average] is also a measure?

Thanks.

[v-xulin-mstf]

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!

[mussaenda]

it requires an aggregation for [average]