cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chotu27
Post Patron
Post Patron

% Change calculation

Hi All,

 

I need  % Change calculation as mentioned below table

 

 

% change.PNG

1 ACCEPTED SOLUTION

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
)  

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Phil_Seamark
Microsoft
Microsoft

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) )

image.png

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@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

 

% change.PNG

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
)  

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

@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 viewTable and data view

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?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark Thank you very much i got it worked finally . Your solution is smart

 

 

There is one more similar calculation i need

 

I wanted to compare values  with previous row and flag it such as if current value = previous value then put 1 otherwise put 0

 

 

 

look at screen shot below

if visit 2 score is same as visit 1 score then 1 otherwise 0 

 

score.PNG

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.