Measure total column per year incorrect

[Tevon713]

Hi all. I need help again. I was testing some measures and found out the the total of measure column is incorrect.

With filters on the time period, the per month or row calculation look okay but when on year ie in this case 2021, which is the total is wrong.

I'm not sure how fix this or get around so the total or entire year is actual total not a calculation. Noted when sum synax is fine, it with the divide from one column to another.

Please help and advise. Thank you.

[Ashish_Mathur]

Hi,

35.59 is correct - 9005/253.  What is the problem?  

[Tevon713]

The issue is when the user select entire year and not per month, it will be incorrect. The "Sum Trx/Op Day" is correct, however for the column "(Sum Trx/OP Day)/FTEs"... FTEs should take average and divide. So for year 2021 should be 35.59/1 = 35.59 not 2.97.   

[danextian]

Hi @Tevon713 ,

For Sum Trx/Op Day, the formula is dividing the total Sum Trx by Op Day. What value do you expect? Sum of the monthly divisions? Average of the monthly divisions?

[Tevon713]

The "Sum Trx by Op Day" look okay. Think the issue is at (Sum Trx/Op Day / FTEs), FTEs should take average. If user select entire year, FTEs should be 1 not 12.

[danextian · Accepted solution]

Hi @Tevon713 ,

Try these formulas as a measure

Average of Monthly Divisions = 
AVERAGEX( 
    SUMMARIZE ( 'Table', Table[Year], Table[Month Name], "Div", [Divide Measure] ),
    [Div]
)

Sum of Monthly Divisions = 
SUMX ( 
    SUMMARIZE ( 'Table', Table[Year], Table[Month Name], "Div", [Divide Measure] ),
    [Div]
)

[Tevon713]

Thank you very much @danextian. The average of monthly divisions is what I'm after.