Calculate Variation dynamically

[GoncaloCare]

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 it was doing:

-> for the first value: 

Mat1valor = CALCULATE(SUM(Query3[EUR]), Query3[MAT]="Mat1")

-> for the second value: 

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

The problem is that this is no dynamic. I want people to be able to choose the first MAT value from Query3[MAT] or even if they want to see the quarter, select for example "Qtr01" (quarter 1) from the filters and then the variation period will be "Qtr05" (quarter 5).

Is this possible?

Best regards!

[v-janeyg-msft · Accepted solution]

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.

[GoncaloCare]

Hi @v-janeyg-msft 

Thanks for the reply! 

I was able to make it dynamic, but now I have another issue. Here is the post I made (there is a comment with a link with the file). https://community.powerbi.com/t5/Desktop/Need-some-help-with-calculated-measures-with-filters/m-p/18...

Hope you can help me.

Regards

[v-janeyg-msft]

Hi, @GoncaloCare 

Due to the nature of the work, I can't interfere with other cases. For your other needs, what I want to say is that if you use 'filter on all pages', the context will be fixed and there is no way to change it so it maybe cause problems. You can try to use 'filter on this visual' or 'filter on this page'.
My suggestion is that you can use 'sync slicers' in multiple pages to achieve the same effect, but you can also unfilter a visual individually.

Reference:

Slicers in Power BI - Power BI | Microsoft Docs

Change how visuals interact in a report - Power BI | Microsoft Docs

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.

[Anonymous]

@GoncaloCare 

I do understand you can select the entry for the first measure. But to be able to select an entry for the second measure... you have to have another table. So, in a word, one table for the selection of the first measure, a second independent table for the selection for the other measure. This is how you do it.

Alternatively, you could have one table with combinations like ("Mat1", "Mat2") where a selection will immediately fix the entry for the first measure and the second.

It's up to you how you want to do it.

[GoncaloCare]

Hi. So, I have the table for the second option. 

If I select this option on the filters:

How can I check on the measure which value is selected, and on the variation, get the MAT2 using the new table with the combinations?

[Anonymous]

Everything is possible with DAX... This is not dynamic since you've hard-coded the name "Mat1 into the measure. And then, again, you've hard-coded the other name "Mat2" into the other measure. No wonder this is static.

[GoncaloCare]

Hi @Anonymous 

Thanks for the reply.

I know I have hard-coded. My question is how to make it dynamic, because I don't have an Idea. I mean, I know how to make it  dynamic for the first value (just selecting the mat 1 in the filters), but I don't know how to make it dynamic on the variation value.

The hard-code was the only way I was able to do to have an example.