Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

TOTALYTD accumulating when it's not supposed to

 

Hello, I'm using the TOTALYTD quick measure to calculate the year-to-date cummulative value of my MTBF metric but when i have no MTBF values, I was expecting the MTBF (h) YTD stay the same, but instead it's showing me increasing values and I don't know why or where they come from, any clue?

 

Here is the measure:

MTBF (h) YTD = 
TOTALYTD([MTBF (h)],'Calendar'[Date])

 

Here's what's happening for my 'Calendar' [Date] selection

killjoy_0-1672658047727.png

killjoy_1-1672658103362.png

 

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi , @Anonymous 

Oh, sorry for not detailed explanation. Not for all the DIVIDE() function is not support for the TOTALYTD() function, just when the denominator is not dynamic. If the denominator is static , it works good,like this:

test2 = TOTALYTD([MTBF (h)],'Calendar'[Date])

vyueyunzhmsft_0-1672794950090.png

For this reason, the actual logic is like this:

CALCULATE(DIVIDE( SUM('Table'[Column2]) - SUM('Table'[Column1]) , 2) ,  DATESYTD('Date'[Date])  )

If the denominators are different, then in different filtering contexts, the sum will not be performed but the average will be performed.

And the bottom layer of TOTAL YTD() is also implemented using the FILTER () function, so it is recommended that you use the above method for some special cases.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

View solution in original post

10 REPLIES 10
v-yueyunzh-msft
Community Support
Community Support

Hi , @Anonymous 

Oh, sorry for not detailed explanation. Not for all the DIVIDE() function is not support for the TOTALYTD() function, just when the denominator is not dynamic. If the denominator is static , it works good,like this:

test2 = TOTALYTD([MTBF (h)],'Calendar'[Date])

vyueyunzhmsft_0-1672794950090.png

For this reason, the actual logic is like this:

CALCULATE(DIVIDE( SUM('Table'[Column2]) - SUM('Table'[Column1]) , 2) ,  DATESYTD('Date'[Date])  )

If the denominators are different, then in different filtering contexts, the sum will not be performed but the average will be performed.

And the bottom layer of TOTAL YTD() is also implemented using the FILTER () function, so it is recommended that you use the above method for some special cases.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

v-yueyunzh-msft
Community Support
Community Support

Hi , @Anonymous 

I test the DIVIDE() function, in my side , when i use the TOTALYTD() function, it return the same as yours.

For this , you can try to use this method:
(1)This is my test data:

vyueyunzhmsft_0-1672713409369.png

(2)I create a measure as [MTBF(h)] like you:

MTBF (h) = DIVIDE( SUM('Table'[Column2]) - SUM('Table'[Column1]) , SUM('Table'[Column3]))

(3)We can create a measure like this to get the right YTD value:

test = var _cur_date = MAX('Calendar'[Date])
var _t = SUMMARIZE(ALL('Calendar') , 'Calendar'[Date] , "MTBF (h)", [MTBF (h)])
var _t2 = FILTER( _t , [Date]<=_cur_date && YEAR([Date]) = YEAR(_cur_date))
return
SUMX(_t2,[MTBF (h)])

(4)Then we can put the fields we need on the visual and we can meet your need:

vyueyunzhmsft_1-1672713476795.png

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Anonymous
Not applicable

Thank you so much for your help, but does that mean the TOTALYTD does not work if I use the DIVIDE()?  I have another measure similar to MTBF that usess DIVIDE and the TOTALYTD there is working as I expected. 

 

 

Anonymous
Not applicable

I see, but in the example you have you're using the sum of values from a column, and I'm using a measure as the <expression> for TOTALYTD.

Even If I switch my simple SUM with a measure (containing SUM), result is the same:

Migasuke_0-1672669430000.png

 



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Anonymous
Not applicable

I don't know what I'm doing wrong then... My "MTBF(h)" is just a divide with a subtraction :

DIVIDE(
    [#M1] - [#M2] - [#M3] - [#M4],
    [#M5]
    )

 

How do you calculate M1, M2...?

Example you have above should not make any problems.



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Anonymous
Not applicable

They're all calculated in a similar way: 

 

M1 =
SUM(Table1[ColumnA])

The rest are as like:

# M2,M3,M4.. = 
CALCULATE(
    SUM(Table2[ColumnB]),
    'Table3'[ColumnC] in {'x','y','z'}
)

Frankly only thing which I have in mind is , that one of the "M" measures contains values. That would explain why you see some changes.
Otherwise try to debug the DEVIDE step by step.



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Migasuke
Super User
Super User

Hi @Anonymous ,

I think the cause might be done by the expression in the other measure yoou have

[MTBF (h)]


I used the same example with simple sum and everything works well:

Migasuke_0-1672667132676.png

 



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors