Forum Discussion

Paulo123's avatar
Paulo123
Frequent Visitor
4 years ago
Solved

avarage

Hi, I want to calculate the average of the years considering the value of each month, with each month being the result of a sum, how can i do this in PB 01/02/2018 221 01/03/2018 893 01/0...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Paulo123 
    You need to iterate over the Year-Month column not the Month column. Something like

    - Med =
AVERAGEX ( VALUES ( CALENDARIO[Year Month] ), [SOMA SAIDA] )
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Paulo123 , Assuming you want some for month and average post that.

Have month year and year in this table of date tbale 

 

AverageX(values('Date'[Month Year]), calculate(Sum(Table[Value])) )

 

Sum till month level avg post that. Use year in visual


To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • Paulo123's avatar
    Paulo123
    Frequent Visitor
    4 years ago

    hi,

    thanks, I got the correct values ​​for each year, but if I just want an average considering all the months of the selected years, how do I do that?

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi, Paulo123 

       

      In theory, the value of measure will change with the context. Can you show how you lay it out in the visual? What's the formula you used in the end?

       

      Janey

      • Paulo123's avatar
        Paulo123
        Frequent Visitor
        4 years ago

        Hi, first i make a sum:

         -SOMA SAIDA = CALCULATE(SUM(EX[VALOR]),E_S[Column1]="S")

        second i calculate the average:

        - Med = AVERAGEX(VALUES(CALENDARIO[Date].[Mês]),[SOMA SAIDA]).

        .

        here we can see that the sum is working correctly:

        changing the layout, i get the annual average correctly:

        but i want to get an average considering all months of all selected years, as the result of this excel: