Forum Discussion

timur-1993's avatar
timur-1993
New Member
2 years ago
Solved

Calculate column subtotal for ratio

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



Need Help
  • BI-Leo's avatar
    BI-Leo
    2 years ago

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

4 Replies

  • BI-Leo's avatar
    BI-Leo
    Frequent Visitor
    2 years ago

    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.

  • BI-Leo's avatar
    BI-Leo
    Frequent Visitor
    2 years ago

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