Column total as average

[jonathanmathes]

I have a table with two columns of data: Absenteeism and Turnover.

In the total of the Absenteeism column, the BI is bringing the average of the months. Already in the total of the Turnover column he is doing the sum.

I need him to also average the turnover and not the sum.

Can anyone help me?

[Anonymous]

Hi @jonathanmathes ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @jonathanmathes ,

Is there any update?

Best Regards,
Eyelyn Qin

[jonathanmathes]

Good Morning,
Sorry for the delay, I was involved in another personal project.

I did as you said using IfInScope.
The final totalizer was OK, but the column one still adds up.

.ROTavg =

if(isinscope('Calendar'[.Ano]) && Not(Isinscope('Calendar'[.MesDescricao])) , averageX(summarize('Calendar','Calendar'[.Ano],'Calendar'[.MesDescricao],"_1",[.TurnoverPeriodo]),[_1]),[.TurnoverPeriodo])

[PaulDBrown]

Try:

ROTavg =
IF (
    ISINSCOPE ( 'Calendar'[.MesDescricao] ),
    [.TurnoverPeriodo],
    AVERAGEX (
        SUMMARIZE (
            'Calendar',
            'Calendar'[.Ano],
            'Calendar'[.MesDescricao],
            "_1", [.TurnoverPeriodo]
        ),
        [_1]
    )
)

[amitchandak · Accepted solution]

@jonathanmathes , You can switch total to avg or sum depending on need using isinscope

if(isinscope(Table[Year]) && Not(Isinscope(Table[Month])) , sumx(summarize(Table, Table[Year], Table[Month], "_1", [measure]), [_1]), [Measure])

or

if(isinscope(Table[Year]) && Not(Isinscope(Table[Month])) , AverageX(summarize(Table, Table[Year], Table[Month], "_1", [measure]), [_1]), [Measure])

[jonathanmathes]

I have never used isinscope, I will read about it and test it.
post the result later

Thanks