Forum Discussion

jonathanmathes's avatar
jonathanmathes
Regular Visitor
4 years ago
Solved

Column total as average

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 h...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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's avatar
jonathanmathes
Regular Visitor
4 years ago

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's avatar
PaulDBrown
Community Champion
4 years ago

Try:

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