cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chirag4370
Frequent Visitor

want to find Delta on the calculated column/Measure between two months.

want to find Delta on the calculated_column/Measure between two months. calculated_column or Measure contains values derived as a percentage of "brk amt" to "total amt". 

is it possible that Delta value changes when we apply it to the matrix in PBI Desktop and pivot it on Months/Years/Units and till it gives Delta with Previous Months/Years/Units?

Thank You in Advance. 

dateunittotal amtbrk amt%brk amt
01-01-2020a6346.35%
01-02-2020a60610.00%
01-03-2020a61813.11%
01-01-2020c5946.78%
01-02-2020c6822.94%
01-03-2020c6311.59%
01-01-2020b6123.28%
01-02-2020b5823.45%
01-03-2020b59610.17%
01-01-2020d63711.11%
01-02-2020d6134.92%
01-03-2020d6246.45%

 

1 ACCEPTED SOLUTION

Hi @Chirag4370 ,

 

Sorry for the confusion, try the following metric:

 

Delta variation =
VAR PYDelta =
    CALCULATE ( [Delta Value], DATEADD ( 'Table'[date], -1, MONTH ) )
RETURN
    IF ( PYDelta <> BLANK (), ( [Delta Value] - PYDelta ) * 100 )

MFelix_0-1664315812924.png

If you want you can add the formatting has follow:

MFelix_3-1664315910559.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

Hi @Chirag4370 ,

 

Add the following measure to your model:

Delta Value = DIVIDE(SUM('Table'[brk amt]), SUM('Table'[total amt]))

 

Being a measure this is calculated based on context:

MFelix_0-1664302479878.png

Has you can see the last row presents the total of 49/738 = 6,64%

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



I already done that. Now, I want to add one more column to the metrix which is delta/difference of that percentage value between current and previous month and similarly for year also(here dummy data only have single year data, but actually it has many years). e.g. for Jan 2020 delta of a is 0. for b it is -3.07 and for c 3.5

 

Basically, I need to measure performance of each unit in every month and for year also, for that I want to compare percentage over months and year.

Hi @Chirag4370 ,

 

Sorry for the confusion, try the following metric:

 

Delta variation =
VAR PYDelta =
    CALCULATE ( [Delta Value], DATEADD ( 'Table'[date], -1, MONTH ) )
RETURN
    IF ( PYDelta <> BLANK (), ( [Delta Value] - PYDelta ) * 100 )

MFelix_0-1664315812924.png

If you want you can add the formatting has follow:

MFelix_3-1664315910559.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you for your precious time and effort.

 

I have query in solution. delta variation remain blank if previous Month is missing in data set. how to fix it in a way that, compare delta with next available month and get delta variation accordingly.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.