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

Total Sum of IF Statement

Hi I am calculating Growth based on two measures and conditions, I am able to see the calculation in the column but am unable get a total of the same column. Any help would be appreciated.

Measure: 

Growth = IF([PY TTM M-1] = 0 && [TTM M-1] > 0, CALCULATE([TTM M-1] - [PY TTM M-1]),0)

11.PNG
 
Thanks!
1 ACCEPTED SOLUTION
Kristjan76
Responsive Resident
Responsive Resident

This is because the measure [PY TTM M-1] = 493.2633.663 in the total row, since this does not equal zero, the if statement returns FALSE, hence = 0.

 

If you want to sum over the Growth then you could do something like this. I did not test this so there might be spelling errors.

 

Growth 2 = 
IF(
  HASONEVALUE([Global Customer Number]),
  [Growth],
  SUMX(  
    ADDCOLUMNS(
      SUMMERIZE(
        myTable,
        myTable[Global Customer Number]
      ),
      "growth val", [Growth]
    ),
    [growth val]
 )

 

View solution in original post

3 REPLIES 3
themistoklis
Community Champion
Community Champion

@Anonymous

 

Put calculate in front of the if statement:

 

 

Kristjan76
Responsive Resident
Responsive Resident

This is because the measure [PY TTM M-1] = 493.2633.663 in the total row, since this does not equal zero, the if statement returns FALSE, hence = 0.

 

If you want to sum over the Growth then you could do something like this. I did not test this so there might be spelling errors.

 

Growth 2 = 
IF(
  HASONEVALUE([Global Customer Number]),
  [Growth],
  SUMX(  
    ADDCOLUMNS(
      SUMMERIZE(
        myTable,
        myTable[Global Customer Number]
      ),
      "growth val", [Growth]
    ),
    [growth val]
 )

 

Anonymous
Not applicable

I tried your solution but I ran into memory issues since my dataset is large. Is there a workaround?

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.