cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Calculate column subtotal for ratio

Hi! I am new to DAX and Power BI.

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.

1 ACCEPTED SOLUTION
Frequent Visitor

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

4 REPLIES 4
Frequent Visitor

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

New Member

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?

Frequent Visitor

Hi  @timur-1993  ,

I think the following article will be helpful for you

Why Power BI totals might seem inaccurate

Frequent Visitor

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.

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors