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

% Change calculation

Hi All,

 

I need  % Change calculation as mentioned below table

 

 

% change.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: % Change calculation

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
Highlighted
Microsoft
Microsoft

Re: % Change calculation

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!

Highlighted
Post Patron
Post Patron

Re: % Change calculation

@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

Highlighted
Microsoft
Microsoft

Re: % Change calculation

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

Highlighted
Post Patron
Post Patron

Re: % Change calculation

@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

Highlighted
Microsoft
Microsoft

Re: % Change calculation

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!

Highlighted
Post Patron
Post Patron

Re: % Change calculation

@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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors