How to sum up a measure based on different levels?

[sagdec12]

Hi everyone, I am facing a problem when sum up a measure on different levels.

I have Location table:

Province	City
P	A
P	B

and product

Product

a

b

I have a already have a measure which calculates the cumulative sales, the result is similiar to:

City	Product	Jan 21	Feb 21	Mar 21	Apr 21
A	a	0	2	4	4
A	b	1	2	2	3
B	a	2	2	3	4
B	b	3	3	4	4

now I want to count the number of products where the total value is greater than 4 based on product, which means

City	Product	Jan 21	Feb 21	Mar 21	Apr 21
A	a	0	0	1	1
A	b	0	0	0	0
B	a	0	0	0	1
B	b	0	0	1	1

and I want the matrix can show on different levels, which means I can see on Province level:

Province	Jan 21	Feb 21	Mar 21	Apr 21
P	0	0	2	3

and also on City level

City	Jan 21	Feb 21	Mar 21	Apr 21
A	0	0	1	1
B	0	0	1	2

Because my cumulative sales is measure, the number is wrong when I go up to higher level.

Can anyone help me with it? Thank you very much!

[v-luwang-msft]

Hi @sagdec12 ,

Could you pls share your pbix file ? And remember to remove confidential data.

Best Regards

Lucien

[amitchandak]

@sagdec12 , I think isinscope can help

example 

if(isinscope (Table[City]), [Meausre1], [Measure2])

refer

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/