Creating a measure that is filtered by another measure

[Anonymous]

Hi, and thanks in advance for your help 😄

What i'm trying to do with this measure 

CY Volume-NoNewLost = CALCULATE([CY Volume];FILTER('Source PriceVolMix';[PY Net Sales]<>0 && [CY Net Sales]<>0))

is to exclude from my analysis those products that have no sales or quantity in any given year. To do this i'm using two previously calculated measures as filters: Net Sales from 2018 and 2019 ([PY Net Sales] and [CY Net Sales] respectively), and work fine.

The issue is, when I drop this newly calculated measure in a visual it returns nothing. Only an empty box.

The data look something like the one below.

Thanks again!

Year	Product	Net Sales	Quantity	Brand	BU
2018	A1000	1000	10	Red	Alpha
2018	A1001	2000	200	Red	Alpha
2018	A1002	100	100	Green	Alpha
2018	A1003	5000	20	Green	Alpha
2018	A1004	10000	1000	Blue	Beta
2018	A1005	1000	50	Blue	Beta
2019	A1000	1500	15	Red	Alpha
2019	A1001	800	150	Red	Alpha
2019	A1002	0	0	Green	Alpha
2019	A1003	7500	40	Green	Alpha
2019	A1004	10100	1500	Blue	Beta
2019	A1005	7500	25	Blue	Beta
2019	A1000	-1000	10	Red	Alpha

[Mariusz]

Hi @Anonymous 

You can try something like this.

Measure = 
VAR _allProductYear = ALL( 'Table'[Product], 'Table'[Year] )
VAR _groupProductYear = GROUPBY( _allProductYear, 'Table'[Product], 'Table'[Year] )
VAR _filterProductYear = FILTER( _groupProductYear, CALCULATE( SUM( 'Table'[Quantity] ) ) = 0 )
VAR _filterProduct = CALCULATETABLE( VALUES( 'Table'[Product] ), _filterProductYear )
RETURN 
CALCULATE(
    SUM( 'Table'[Quantity] ),
    EXCEPT(
        VALUES( 'Table'[Product] ),
        _filterProduct
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous · Accepted solution]

Hey there,

Without having seen the actual data, experience tells me the issue is that there aren't any rows in your table that fulfill the filter condition. Sintactically, the formula looks correct.

Maybe you could try using ISBLANK ( [PY Net Sales] ) instead of checking whether it's equal to 0.

Hope this helps.

Alex