topic Re: Difference & % difference in Connectivity & Data Prep

Difference & % difference

Anonymous — Tue, 19 Dec 2017 16:14:52 GMT

Hello,

Can you please help me in getting the correct difference & the % difference of two numbers (column GM 2016; column GM 2017),

I want to obtain the difference only to non 0 number that are present in both columns.

I used "fabs(Sum({$<[Year]={"2016"}>}[Gross Margin Actual Year])) - (Sum({$<[Year]={"2017"}>}[Gross Margin Actual Year]))"

to get the difference as the data in excel is in one column.

Thank you in advance

Sachin

Re: Difference & % difference

sunny_talwar — Tue, 19 Dec 2017 16:47:49 GMT

May be this

If(fabs(Sum({$<[Year]={"2016"}>}[Gross Margin Actual Year])) <> 0 and fabs(Sum({$<[Year]={"2017"}>}[Gross Margin Actual Year])) <> 0, fabs(Sum({$<[Year]={"2016"}>}[Gross Margin Actual Year])) - (Sum({$<[Year]={"2017"}>}[Gross Margin Actual Year])))

Basically check if part 1 of the expression is not 0 and part 2 is not zero, then perform the calculation

Re: Difference & % difference

Anonymous — Wed, 20 Dec 2017 10:34:57 GMT

Hello Sunny,

Thank you for your response, it worked well.

I have one more question.

I want to create a bar chart and show only the numbers that are zero in GM 2016. This means the numbers are only in GM 2017 ( not the numbers that are present in both). As shown in figure below.

I tried to use this formula but i failed.

If(fabs(Sum({$<[Year]={"2016"}>}[Gross Margin Actual Year])) <> 0 , Sum({$<[Year]={"2017"}>}[Gross Margin Actual Year]) ,0)

Can you please help me in this,

Thank you

Sachin

Re: Difference & % difference

sunny_talwar — Wed, 20 Dec 2017 16:49:58 GMT

May be this

If(fabs(Sum(TOTAL {$<[Year]={"2016"}>}[Gross Margin Actual Year])) = 0 , Sum({$<[Year]={"2017"}>}[Gross Margin Actual Year]), 0)

Re: Difference & % difference

Anonymous — Thu, 21 Dec 2017 13:59:34 GMT

This doesnot work,

It gives all rows that are 0

Re: Difference & % difference

sunny_talwar — Thu, 21 Dec 2017 14:01:25 GMT

Then this?

If(fabs(Sum(TOTAL {$<[Year]={"2016"}>}[Gross Margin Actual Year])) > 0 , Sum({$<[Year]={"2017"}>}[Gross Margin Actual Year]), 0)

Re: Difference & % difference

Anonymous — Thu, 21 Dec 2017 14:06:19 GMT

No, i have tried this and this gives the results of both 2016 & 2017 non zero numbers.

Re: Difference & % difference

Anonymous — Thu, 21 Dec 2017 14:10:52 GMT

also tried this

If(fabs(Sum(TOTAL [Gross Margin Actual Year])) <> 0 , Sum({$<[Year]={"2017"}>}[Gross Margin Actual Year]), 0)

and this gives all the values of 2017 (which are in 2016 & 2017, but the numbers of 2017). But what i need is the values of only 2017 that means it may be new row or 2016 = 0

Re: Difference & % difference

sunny_talwar — Thu, 21 Dec 2017 14:20:17 GMT

Can you, once again, put it in words as to what you are looking to get?

Re: Difference & % difference

Anonymous — Thu, 21 Dec 2017 14:25:55 GMT

the column GM 16 & GM 17 are in one column in excel. so I want to a bar graph that shows only the numbers of GM 17 which are highlighted in yellow. The criteria here is that these numbers are zero in 2016. So I want thee numbers in graph not the ones that are in both the columns.

Re: Difference & % difference

sunny_talwar — Thu, 21 Dec 2017 14:29:14 GMT

Is this the expression you use for GM 16?

fabs(Sum({$<[Year]={"2016"}>}[Gross Margin Actual Year]))

If you are, then you want this, right?

If(GM 16 = 0, GM17, 0)

If this is true, then I would think that one of these expression should work

If(fabs(Sum({$<[Year]={"2016"}>}[Gross Margin Actual Year])) = 0, Sum({$<[Year]={"2017"}>}[Gross Margin Actual Year]), 0)

or this

If(fabs(Sum(TOTAL {$<[Year]={"2016"}>}[Gross Margin Actual Year])) = 0, Sum({$<[Year]={"2017"}>}[Gross Margin Actual Year]), 0)

If this doesn't work still, its time for you to share a sample so that we can see what in the world you are doing