Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The total in my Pivot table is wrong when it's open and correct when it's closed.
In a table it's okay
How do you correct this?
Hello,
what is the formula used in your expression ?
Regards,
the formula for the total "Operating Expenses" is :
num(SUM({$<[AccountCC5 Code]={OPEX},[Calendar Year]={$(=max([Calendar Year])-1)}, [Calendar Day Of Year] = { "<=$(=DayNumberOfYear(vDateRefLY))"}, 1=1>}[GL JE Line Balance $(vCurrency)] / vCurrencyDivider * [AccountCC Multiplier]) , vCurrencyFormat)
it is coming from a big one :
if(QBR_Dim2='Total Sales', $(vTotalSales4),
if(QBR_Dim2='Misc Discount', $(vMiscDiscount4),
if(QBR_Dim2='Commercial Net Sales', $(vCommercialNetSales4),
if(QBR_Dim2='Bonus and discounts granted', $(vBonusanddiscountsgrante4),
if(QBR_Dim2='Net Sales', $(vNetSales4),
if(QBR_Dim2='Net Sales %', num($(vNetSales4)/$(vTotalSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='Cost of goods sold', $(vCostofgoodssold4),
if(QBR_Dim2='Cost of goods offered', $(vCostofgoodsoffered4),
if(QBR_Dim2='Other cogs', $(vOthercogs4),
if(QBR_Dim2='Absorption Difference', $(vAbsorptionDifference4),
if(QBR_Dim2='Variance', $(vVariance4),
if(QBR_Dim2='Gross Margin', $(vGrossMargin4),
if(QBR_Dim2='Gross Margin %', num($(vGrossMargin4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='Personal Expenses', $(vPersonalExpenses4),
if(QBR_Dim2='Commercial Expenses', $(vCommercialExpenses4),
if(QBR_Dim2='Structural Costs', $(vStructuralCosts4),
if(QBR_Dim2='Personal Expenses %', num($(vPersonalExpenses4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='Commercial Expenses %', num($(vCommercialExpenses4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='Structural Costs %', num($(vStructuralCosts4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='EBITDA', $(vEBITDA4),
if(QBR_Dim2='EBITDA %', num($(vEBITDA4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='EBIT', $(vEBIT4),
if(QBR_Dim2='EBIT %', num($(vEBIT4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim2='EBT', $(vEBT4),
if(QBR_Dim2='EBT %', num($(vEBT4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'),
if(QBR_Dim1='Operating Expenses', $(vQBR4),
if(QBR_Dim1='Operating Expenses %', num($(vOPEX4)/$(vNetSales4),'#`##0.0%;(#`##0.0)%'))))))))))))))))))))))))))))