Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sagdec12
Employee
Employee

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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.