Forum Discussion

GoncaloCare's avatar
GoncaloCare
Helper I
5 years ago
Solved

Calculate Variation dynamically

Hi.  I need some help. So, imagine this. The first value is the Moving Annual Total from MAT1. The value below is the variation vs homologous period (MAT 2 in this case).   The way I did ...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, GoncaloCare 

     

    If I understand what you mean, Do you want the value of Query3[MAT] to be dynamic?

    You can try to create a single distinct list(distict(Query3[MAT])), then use this new table column as slicer. And use selectedvalue function to distinguish different values in two different tables.

     

    -> for the first value: 

Mat1valor = CALCULATE(SUM(Query3[EUR]), Query3[MAT]=selectedvalue(Query3[MAT]))
-> for the second value: 

VariacaoMAT1 = ( ( [Mat1valor] / CALCULATE(SUM(Query3[EUR]),selectedvalue(table[MAT]) ) - 1 ))

     

    If it doesn’t solve your problem, please share some sample fake data and your desired result,then feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
5 years ago

Hi, GoncaloCare 

 

If I understand what you mean, Do you want the value of Query3[MAT] to be dynamic?

You can try to create a single distinct list(distict(Query3[MAT])), then use this new table column as slicer. And use selectedvalue function to distinguish different values in two different tables.

 

-> for the first value: 

Mat1valor = CALCULATE(SUM(Query3[EUR]), Query3[MAT]=selectedvalue(Query3[MAT]))
-> for the second value: 

VariacaoMAT1 = ( ( [Mat1valor] / CALCULATE(SUM(Query3[EUR]),selectedvalue(table[MAT]) ) - 1 ))

 

If it doesn’t solve your problem, please share some sample fake data and your desired result,then feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.