% in a measure not adding up

[katyfailoo]

Hi All,

I have two tables

Table A

Listed below is how it looks like

Reason	Date	Employee ID	Year	Flag
A	1/1/2021	12341	2021	1
B	1/1/2019	12421	2019	1

Table B

It is just a calendar table

Both tables have a relationship on date

Goal is to get the % if I am under or over # of employees. 

My formula is as follows:

Over and Under=

Var lastyears = calculate(sumx(tablea, table[employee flag]), filter(tablea, table[year] <MAX(tablea[year])))

Var totalsumthisyear = calculate(sumx(tablea, table[employee flag]), filter(tablea, table[year] ==MAX(tablea[year])))

Return

totalsumthisyear/(lastyears/3) ** Note I added divided by three because that's how many years I have of data that is not 2021

So far I got my % and what I want

However if I pull in the values below in a table

• Column from Date Table B
• Measure over and under

Problem 1) Only works with Month and not Year

Problem 2) I can't get the %'s shows to add up.

Date (From Calendar table b)	Over and Under
Jan	123%
Feb	45%
March (list goes on)	10%
TOTAL	12% *Random # that does not add up.

Why doesn't my measure add up in a matrix? How do I get my measure to add up in a matrix? Should I write another calculation?

Also, how do I get the count of n years? so I don't have to manually add in 3? Eventually 2022 will come around and formula needs to be adjusted to 4 

Appreciate the help!

[v-henryk-mstf]

Hi @katyfailoo ,

For the problem you describe, you may wish to refer to the following blog, I hope it will help you.

Dealing with Measure Totals - Microsoft Power BI Community

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[AlexisOlson]

If you were 25% over every single month in a year, do you expect the total to be the average amount you were over for the year (25%), or do you actually want to show 12 * 25% = 300%? How do you interpret that 300%?

[katyfailoo]

Hi Alexis, 

Thank you for your response! For the year it would be the average.

So far I have the percentages for each month (using my calculation); however they don't add up. I want to add those % for each month, so that I can use the total and divide it by the # of months I have.