Forum Discussion

chotu27's avatar
chotu27
Post Patron
8 years ago
Solved

% Change calculation

Hi All,

 

I need  % Change calculation as mentioned below table

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi chotu27

     

    I'm doing it as a calculated measure this time.

     

    Measure = 
VAR LastVisit = 
    CALCULATE(
        MAX('Table2'[Visit Name]),
        FILTER(
            ALL('Table2'),
            'Table2'[Subject Number] = MAX('Table2'[Subject Number]) && 'Table2'[Visit Name] < MAX('Table2'[Visit Name])
            ))

VAR Visit1 = 
    CALCULATE(
        SUM('Table2'[Score]),
        FILTER(
            ALL('Table2'),
            'Table2'[Subject Number] = MAX('Table2'[Subject Number]) &&
            'Table2'[Visit Name] = LastVisit
            )
        )
VAR Visit2 = SUM('Table2'[Score])        
RETURN DIVIDE(
            Visit2 - Visit1 ,
            Visit1
)

6 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi chotu27

     

    This calculated column seems to do the trick with your sample data.  I have attached a PBIX model.

     

    % Change = 
VAR ThisValue = 'Table1'[Sales]
VAR LastValue = MINX(FILTER('Table1','Table1'[Sales] > ThisValue),'Table1'[Sales])
RETURN 
    IF(ISBLANK(LastValue),1,
     - DIVIDE( 
         (LastValue - ThisValue ) , LastValue) )

     

     

    • chotu27's avatar
      chotu27
      Post Patron
      8 years ago

      Phil_Seamark Good One !  But Some how it is not working for my dataset

       

      Please check below data.

       

      % change cal =  current visit -previous visit/ previous visit

      Suppose for S1051 calculation i s=  Visit 2 - Visit 1/Visit1 which is 80-100/100 =  - 0.20 

       

      I need a calculation like above

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        Hi chotu27

         

        I'm doing it as a calculated measure this time.

         

        Measure = 
VAR LastVisit = 
    CALCULATE(
        MAX('Table2'[Visit Name]),
        FILTER(
            ALL('Table2'),
            'Table2'[Subject Number] = MAX('Table2'[Subject Number]) && 'Table2'[Visit Name] < MAX('Table2'[Visit Name])
            ))

VAR Visit1 = 
    CALCULATE(
        SUM('Table2'[Score]),
        FILTER(
            ALL('Table2'),
            'Table2'[Subject Number] = MAX('Table2'[Subject Number]) &&
            'Table2'[Visit Name] = LastVisit
            )
        )
VAR Visit2 = SUM('Table2'[Score])        
RETURN DIVIDE(
            Visit2 - Visit1 ,
            Visit1
)