cancel
Showing results for
Did you mean:
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?

 date unit total amt brk amt %brk amt 01-01-2020 a 63 4 6.35% 01-02-2020 a 60 6 10.00% 01-03-2020 a 61 8 13.11% 01-01-2020 c 59 4 6.78% 01-02-2020 c 68 2 2.94% 01-03-2020 c 63 1 1.59% 01-01-2020 b 61 2 3.28% 01-02-2020 b 58 2 3.45% 01-03-2020 b 59 6 10.17% 01-01-2020 d 63 7 11.11% 01-02-2020 d 61 3 4.92% 01-03-2020 d 62 4 6.45%

1 ACCEPTED SOLUTION
Super User

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

If you want you can add the formatting has follow:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

4 REPLIES 4
Super User

Hi @Chirag4370 ,

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

Being a measure this is calculated based on context:

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

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.

Super User

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

If you want you can add the formatting has follow:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Frequent Visitor

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.

Announcements