Adding the percentual variance between columns on crosstabs

[Renan_Crepaldi]

Hi, everyone!

I'm trying do add a column with a simple calculation on my crosstab, but I haven't find a way to do it.

I want to make a percentual variance between the two periods I'm comparing (2020 vs. 2019). It should be like this:

I don't know if this changes anything, but I've selected the option to show the headers of the measures on the rows.

Is there a way to do it on the crosstab? Or i should use another type of object?

Thanks!

[PetriRoine]

Hello @Renan_Crepaldi ,

Yes, you can do this in Crosstab. You just need to use aggregated measure:

( Sum [_ByGroup_] ('Y2020'n) / Sum [_ByGroup_] ('Y2019'n) ) - 1

 And here is how it looks like plus the source data I used:

I hope this helps,

Br,

Petri

[Renan_Crepaldi]

Hello, @PetriRoine! Thanks for replying.

The problem is that my data doesn't have the KPIs separated by year. I needed to use the crosstab to split each KPI by putting the year on the column.

I've attached a sample to make it easier to understand (should've done this before, sorry).

If you need anymore information, please, let me know.

BR, Renan

[PetriRoine · Accepted solution]

Hello @Renan_Crepaldi ,

With the data structure you have, personally I don't think it's doable with any out-of-the-box chart or custom build graph. The way I see it, you have two options to achieve this:

1. Adjust the data structure
2. If you have one of the newer versions of VA then you could use Data-Driven Content and any JavaScript charting framework to create this.

Best regards,

Petri

[Renan_Crepaldi]

Thanks for the tip, Petri.

I'll try to adapt the structure of my table.

Regards,
Renan