How can I calculate % from 2 columns 1 and 2 where 1,2 are the periods of time (year, months, etc)

[Tatmel]

Hi All,

I have following scenario:

I am using a simple table visual where in Rows I put Categories and in Columns I put periods of time.

I would like to create measure that calculates % of growth of each category:

Category	2015	2016	2017	% 15_16	% 16_17
A	10	15	12	50%	-20%
B	5	3	2	-40%	-33%
C	15	8	14	-47%	75%

Columns related to years are changing based on slice period filters.

Thank you in advance for sharing your ideas about it.

KR,

Tatiana

[v-yulgu-msft]

Hi @Tatmel,

Based on my understanding, you used a matrix visual to display data, and you added the year column into a slicer. Now, your requirement is to dynamically show/hide the corresponding year and corresponding growth percentage, right?

By default, it is not available to show two measures [%15_16] and [%16_17] like above matrix structure. To workaround this, please refer to my reply in this thread: Matrix Total Column.

Besides, you should be aware that currently it is not supported to conditionally show/hide a column/measure in a visual depend on slicer selection. The slicer can only filter the available values under a single colum (like filter 2015, 2016, 2017), but cannot filter the column names (like % 15_16, % 16_17). For example, if you don't select year 2015 in slicer, in matrix, the column 2015 will disappear, while the column %15_16 is remain.

Regards,
Yuliana Gu

[Tatmel]

Hi Yuliana,

Thank you for the feedback! You understand me correctly.

At this moment I am looking for an idea to use dynamic measure to calculate growth in % between two columns. The name of the column is not important. It can be a kind of Total column but instead of summarise values in row, calculate % of growth.

In my current reports I am using "static" measures where I compare values by different periods.

Kind regards,

Tatiana

[WP]

Hi Tatiana,
If you create a measure based on categories as %of total of all categories,
Adding the years in the columns will add years to the filter context. As from thenyears are considered automatically for your calculation.

[Tatmel]

Hi WP,

Thank you for your idea.

However, I want to see  % of growth of each category (row) in years/months/etc. If I choose % of total of all categories I do not reach my goal. 

Kind regards,

Tatiana

[WP]

Hi Tatiana,

somehow I missed that 🙂 If you want to compare values with previous periods, you can use timeintelligence functions. If you want to compare same periods over the years, it would be sameperiodlastyear.For timeintelligence to work you need to have a proper date table in your data model.

You would need three measures:

1. calculate values for current period
2. calculate value for similar period last year
3. divide(current period measure,similar prior period measure,'some default value you want to show if error') 

If these can be static, you can also calculate values for when year = 2016, values for when year = 2017 etc... and use these values in your divide functions.

You would not need to create these measures for each category as long as categories are made part of filter context, e.g. by adding them to the rows of the table.