tableau-apiaggregate-functionslevel-of-detail

How to calculate percent difference for an aggregated measure in Tableau?


I am trying to calculate the percent different in net sales between current period and prior period. I am able to calculate the percent difference for a measure that is not aggregated. However, in my case, net sales is calculated based on the below formula which makes it an aggregate measure:

SUM([Gross Sales])-ZN(SUM([Promotions]))-ZN(SUM([Refund_Amount]))-ZN(SUM([OrderTax]))-ZN(SUM([Shipping Tax]))

Below are the other formulae I am using

Prior period sales: IF ATTR([In Prior Period?]) THEN [Net Sales] END

Current period sales: IF ATTR([In Current Period?]) THEN [Net Sales] END

% Diff Net Sales: ([Current Period Sales]-[Prior Period Sales]) /[Prior Period Sales]

However, I am not getting any values for % Diff Net Sales. However, when using a non-aggregated measure, saying Unit Sales instead of Net Sales, I am getting the correct value. In this case, I use the formula:

Current period sales: SUM(IF [In Current Period?] = True THEN [Unit Sales] END)

These are the calculations and data structure I am using.(Fields with a preceding # are calculated fields and hence aggregated fields) data structure

I appreciate any help with this issue. Gowri

I tried the below formula for current period sales:

(Refunds are obtained from a blended data source, hence slightly different)

SUM(if [In Current Period?] then [Gross Sales] END)
-ZN(SUM(if [In Current Period?] then [Promotions] END))
-ZN((if attr([In Current Period?]) then SUM([Adjustments].[Refunds]) END))
-ZN(SUM(if [In Current Period?] then [OrderTax] END))
-ZN(SUM(if [In Current Period?] then [Shipping Tax] END))   

I am able to get a value for % Diff Net Sales. However, the value seems to be wrong. This seems to be because of a difference in the Current Period Sales and Prior Period Sales value as shown in images below. In one sheet, I am getting correct values, but in another, wrong values.(I have selected January 2021 as the current period using a parameter) These are the correct values. Correct Values These are the wrong values and hence getting a wrong % Diff Net Sales value. Wrong Values

What could be the reason for this and how do I resolve it please?

enter image description here


Solution

  • Bury the date into the Net Sales calcs.

    SUM(if [In Current Period?] then [Gross Sales] END)
    -ZN(SUM(if [In Current Period?] then [Promotions] END))
    -ZN(SUM(if [In Current Period?] then [Refund_Amount] END))
    -ZN(SUM(if [In Current Period?] then [OrderTax] END))
    -ZN(SUM(if [In Current Period?] then [Shipping Tax] END))
    

    and

    SUM(if [In Prior Period?] then [Gross Sales] END)
    -ZN(SUM(if [In Prior Period?] then [Promotions] END))
    -ZN(SUM(if [In Prior Period?] then [Refund_Amount] END))
    -ZN(SUM(if [In Prior Period?] then [OrderTax] END))
    -ZN(SUM(if [In Prior Period?] then [Shipping Tax] END))
    

    Then this will work.

    % Diff Net Sales: ([Current Period Sales]-[Prior Period Sales]) /[Prior Period Sales]