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
Anonymous
Not applicable

Filtered total of measure

Hello,

 

I have the following table visual with Month/Year and Member categories with Measures 1, 2 and 3. Measure 3 is the sum of Measures 1 and 2. I would like to have a second visual with Measure 4 that shows the Month - Year sum of Measure 3 as shown under Visual 2 below but have not been able to find a way to do this.

 

Visual 1     
      
Month - YearMemberMeasure 1Measure 2Measure 3 (M1+M2) 
Jan 2020A549 

Jan 2020

B325 
Jan 2020C718 
Feb 2020A235 
Feb 2020B112 
Feb 2020C437 
 
Visual 2 
  
Month YearMeasure 4 (Month - Year sum of Measure 3)
Jan 202022
Feb 202014

 

Appreciate the help.

 

Steve

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Anonymous , Try

sumx(Table,[measure 3]) or

sumx(values(Table[Month-Year]),[measure 3])

View solution in original post

Hi @Anonymous,

 

Not sure what the measures are doing (or if I'm losing my mind) but on visual 2 could you not just have the same matrix but take out the member column.. they should aggregate to month year level then.

 

Otherwise, this may help

 

SUMX( VALUES( Table[Member] ), [measure 3] )

 

Let me know how you get on.

 

Kris 

View solution in original post

Hi @Anonymous ,

 

Would you please try to use the following measure:

 

measure4 = SUMX(SUMMARIZE('Table','Table'[Month-Year],"_measure3",[measure3]),[_measure3])

 

It will be nice if you can show us some sample data by onedrive for business.

 

Best Regards,

Dedmon Dai

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Anonymous , Try

sumx(Table,[measure 3]) or

sumx(values(Table[Month-Year]),[measure 3])

Anonymous
Not applicable

Hello,

 

I tried the SUMX statements and the result is a number that is much higher than the actual total of Measure 3 for a given Month - Year. The data I'm working with is from a Summarized table if that makes any difference.

 

Thanks,

Hi @Anonymous ,

 

Would you please try to use the following measure:

 

measure4 = SUMX(SUMMARIZE('Table','Table'[Month-Year],"_measure3",[measure3]),[_measure3])

 

It will be nice if you can show us some sample data by onedrive for business.

 

Best Regards,

Dedmon Dai

Hi @Anonymous,

 

Not sure what the measures are doing (or if I'm losing my mind) but on visual 2 could you not just have the same matrix but take out the member column.. they should aggregate to month year level then.

 

Otherwise, this may help

 

SUMX( VALUES( Table[Member] ), [measure 3] )

 

Let me know how you get on.

 

Kris 

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.