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 he is doing the sum.

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

 

 

Can anyone help me?

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])

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    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

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

       

      Thanks

    • 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]
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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