Skip to main content
cancel
Showing results for 
Search instead 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

Reply
timur-1993
New Member

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.
timur1993_0-1703835005586.png

 

 

Thanks in advance



1 ACCEPTED SOLUTION
BI-Leo
Frequent Visitor

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

View solution in original post

4 REPLIES 4
BI-Leo
Frequent Visitor

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

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?

Hi  @timur-1993  , 

I think the following article will be helpful for you

Why Power BI totals might seem inaccurate

BI-Leo
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors