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
)

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
)

    • chotu27's avatar
      chotu27
      Post Patron
      8 years ago

      Phil_Seamark  Excellent It got Worked  but some how i think my table in real contains so many columns so that is why it is not working for me.  

       

      Is your calculation works for only 3 columns or more than that in table?

        Table and data view

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        HI chotu27

         

        When you say it isn't working, do you mean you are expecting a lot more in the final column in your screenshot?