Calculate column subtotal for ratio

[timur-1993]

Hi! I am new to DAX and Power BI.
Please, would you please tell me how it should be done?

I am stuck with a problem.

I have a measure which should calculate sales forecast based on already given days in a month and total number of days. Sth like this
Forecast = Sales / Days_of_sales * number_of_days_in_the_month

So I have the following measures and formulas:

Forecast =DIVIDE ( [Sales], [Sales_days] ) *  [Days_in_month]

Where
Sales is Sum over a sales column

Days_in_month = DATEDIFF(STARTOFMONTH('ОС'[Date]), EOMONTH(MIN('ОС'[Date]), 0), DAY) + 1
shows number of days in a month in consideration

Sales_days = DISTINCTCOUNTNOBLANK ( 'ОС'[Date] )
shows number of days from the beginning of the month until including yesterday.

So here's what I have. Consider first row in the matrix. 13272 and 15008 are correct cells. What I am stuck with is columns subtotals. I want them to be 13272+15008=28820, not 13853.

Thanks in advance

[BI-Leo · Accepted solution]

You can add a varible _Result = SUMX(VALUES('Table'[Month]), Forecast) and return it to see whether it works.

[timur-1993]

Hey, BI-Leo!

This worked just fine, thanks a lot!

Could you give advice on what to read to better understand how to manipulate totals?

[BI-Leo]

Hi  @timur-1993  , 

I think the following article will be helpful for you

Why Power BI totals might seem inaccurate

[BI-Leo]

Hi @timur-1993 , this is due to Content Transition in the DAX, and the total won't simply add all the cell together.

For your scenario, the total calculates the result amonng whole sum sales in Nov and Dec then divide whole days in Nov and Dec.