cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sagdec12
Microsoft
Microsoft

How to sum up a measure based on different levels?

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

 

I have Location table:

ProvinceCity
PA
PB

 

and product

Product
a
b

 

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

CityProductJan 21Feb 21Mar 21Apr 21
Aa0244
Ab1223
Ba2234
Bb3344

 

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

CityProductJan 21Feb 21Mar 21Apr 21
Aa0011
Ab0000
Ba0001
Bb0011

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

ProvinceJan 21Feb 21Mar 21Apr 21
P0023

and also on City level

CityJan 21Feb 21Mar 21Apr 21
A0011
B0012

 

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!

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @sagdec12 ,

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

 

Best Regards

Lucien

amitchandak
Super User
Super User

@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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!