rate of change

[Anonymous]

Hello, 

I want to calculate a rate of change between : my column Result2021 and my column Budget2021.

My column Result2021 in my visuel is a measure

My column Budget2021 is a column in my database

How can I do a DAX formula to do : (Result2021 - Budget2021) / Budget 2021

I have already do this with Result2021 and Result2020 because there are 2 mesures so I have got no problem.

Somebody can help me please ? 

[niteshtrehan89]

Hi, Please try the below measure

Resultset=

var result= [result2021] 

var budget= sum(budget[2021])

return

divide(result-budget,budget)

[Anonymous]

Hi, i have test your measure, here was the result : 

it's not what I thought...

I don't understand why it doesn't work

[Anonymous]

[Anonymous]

The screenshot above is when i put the formula, you can see that there is a problem because for example you can see "Direction Technique Etude" two times without a real rate of change. What I want is like the screenshot below with the name of the sections one time for each with the rate of change next to it. 

Like my column in my table named "VAR R21-R20". I don't know if I expressed myself well and I apologize, I do my best.

[v-easonf-msft]

Hi, @Anonymous 
"Direction Technique Etude" appears twice, but they do not have the same N'(2060, 2070).

Have you checked your formula "Total heures AA"? It seems that column 'R2021' does not have a value on some rows.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi @v-easonf-msft,

My formula do the sum of my column where there are the hours clocked by employees.

[v-easonf-msft]

Hi,  @Anonymous 

Sorry, I  don’t see any errors in the formula.

Can you recheck the value of R2021 where section is "Direction Technique Etude"  and  N' is 2060？

Make sure that the value here is not empty.

If it is convenient, can you share sample files and part of the data for further research?

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi, @v-easonf-msft 

I don't know how I can share the file on the forum in any other way than having it hosted on a site.

Here is the link of the .pbix hosted on the TransferNow site:
https://www.transfernow.net/dl/20210519CeH5O7MN

Sincerely

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Sorry for the late reply.

The formula is  OK. It seems that you just need to change the  cross filter direction to both.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-easonf-msft , 

No problem for the late reply, Thanks !

But I don't understand why I need to put the cross filter direction to both. 

Sincerly,

[v-easonf-msft]

Hi, @Anonymous 

I didn't see  errors from your screenshot.

Can you explain your expected result in detail?

Best Regards,
Community Support Team _ Eason

[Anonymous]

It doesn't work 

[amitchandak]

@Anonymous , across the common dimension of grand total level 

divide( [Result2021] - Sum(Table[Budget2021]) , Sum(Table[Budget2021]))