Issue with Subtotals/formula in a matrix

[enlodi]

Hi guys,

I'm quite desperate with a thing that it didn't seem to be that tricky. 

I've made up a matrix with a FC estimation. The problem is that the subtotals are not correctly calculated. I believe that the confilct is linked with the formula that I've used :

Sales Forecast formula =

VAR

SalesLY= CALCULATE([total sales], DATEADD(DATES[Date],-1,YEAR))

VAR

Sales2YearsAgo= CALCULATE([total sales],DATEADD(DATES[Date],-2,YEAR))

VAR

Sales2020= SUM('Order intake'[true value])

VAR

factor = 0.95

RETURN

IF(ISBLANK(Sales2020),DIVIDE(SalesLY + Sales2YearsAgo,2 ,0) * factor ,

DIVIDE(Sales2020 + SalesLY, 2, 0) * factor)

With the previous formula, I'm getting the correct amounts per month, but the subtotals per quarter and year are not corrects. Indeed, the problem is that the formula above is been applied to the subtotals and year rows (and i don't want that).

Do you now how could I fix it up? I've been trying for two days with anyluck at all.

Kind regards, 

Enrique.

[az38]

@enlodi 

how do you calculate [total sales] ?

[enlodi]

using the following formula:

Total Sales = SUM('Order intake'[true value])

** True value = total sales 

[az38]

@enlodi 

whats formula do you use?

for correct total it should be smth like

=CALCULATE(SUM([Column]))

[enlodi]

Hi mate,

I've just updated my post with the formula 🙂

[amitchandak]

Please share the formula. Can you share sample data and sample output. Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[enlodi]

How could I share that info with you? I'm not finding the option "attach" or something like that. (I'm pretty new in PBI as you could see) 

[amitchandak]

Try like this in sumx or averagex

averagex(summarize('Order intake',Date[Month year],"_Avg",IF(ISBLANK(Sales2020),DIVIDE(SalesLY + Sales2YearsAgo,2 ,0) * factor ,
DIVIDE(Sales2020 + SalesLY, 2, 0) * factor)),[_Avg])

[enlodi]

Thanks for your response 🙂

I've just shared the formula that is working, but is not the case with the subtotals.