cancel
Showing results for
Did you mean:
Highlighted
Member

## YTD total with a measure

Hi, I used the quick measure function to get a YTD total, but it is not getting the exact right number. I don't know why and how to adjust it to work.

I have a year.month column from my calendar.  What I want is a ytd total of the 'Monthly%' column.  The math to get that % is based on the two columns to the right of it ('termed employees' / 'total employees')

RunningTotalYTD is the quick measure:

RunningTotal YTD =
totalytd( [Monthly%],'DateTable1'[Date],
FILTER(
ALLSELECTED('DateTable1'[date]),
ISONORAFTER(('DateTable1'[date]), MAX('DateTable1'[date]), DESC))

The first month is right, but everything after that is a bit less than it should be.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Hi , Based on my test, you could refer to below steps: Sa...

Based on my test, you could refer to below steps:

Sample data:

Create two measures:

`Monthly% = DIVIDE(MAX('Table1'[Termed Employees]),MAX('Table1'[ToTal Employees]))`

`Running Total YTD = CALCULATE(SUMX('Table1','Table1'[Monthly%]),FILTER(ALL(Table1),'Table1'[Year.Month]<=MAX('Table1'[Year.Month])))`

Result:

https://www.dropbox.com/s/mm901axt8eevm9b/YTD%20total%20with%20a%20measure.pbix?dl=0

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Frequent Visitor

## Re: YTD total with a measure

Hi.

I went ahead and tried to something different than a quick measure and I think I believe this is what you might want.

What I ended up doing was creating a custom column using Edit Queries.

Click on Edit Queries.

Click on the Add Column Tab, then click on Custom Column.

Name your column, then enter the formula by selecting the columns from the available columns list and putting the necessary division symbol between them

`[Termed Employees]/[Total Employees]`

The table now has the new column.

Now you want to change the data type to percent, by selecting the column, going to the Transform tab, and changing the data type to 'Percentage'.

I am not sure if this solves your problem, but I hope it helps.

Community Support Team

## Hi , Based on my test, you could refer to below steps: Sa...

Based on my test, you could refer to below steps:

Sample data:

Create two measures:

`Monthly% = DIVIDE(MAX('Table1'[Termed Employees]),MAX('Table1'[ToTal Employees]))`

`Running Total YTD = CALCULATE(SUMX('Table1','Table1'[Monthly%]),FILTER(ALL(Table1),'Table1'[Year.Month]<=MAX('Table1'[Year.Month])))`

Result:

https://www.dropbox.com/s/mm901axt8eevm9b/YTD%20total%20with%20a%20measure.pbix?dl=0

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team