Basic DAX formulas Acum Average

Hey emilux ,

 

please take the time and create a pbix file that contains sample data but still reflects your data model, upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

 

Maybe this article will help, as almost every aspect of date related calculations is covered: Time patterns – DAX Patterns

 

Hopefully, this is helping to tackle the challenge you are facing.

 

Regards,

Tom

Hi emilux 

I see your picture and summarize the calculation formula of the following 4 columns. Please check it.

-

column [-Acum x Ano]: accumulate column [Volumen] according to column 1,2,3;

column [-Acum Gral]: accumulate column [Volumen] according to column 1,2;

column [-Prom x Ano]: average of column [-Acum x Ano]

column [-Prom Gral]: average of column [-Acum Gral]

Right?

If yes, create the measures bellow. 

-

 

-Acum x Ano =
CALCULATE(SUM('Table'[Volumen]),FILTER(ALLEXCEPT('Table','Table'[Ciudad],'Table'[Cod Trx],'Table'[Ano]),'Table'[Mes] <= SELECTEDVALUE('Table'[Mes])))

-Acum Gral =
var _anomes=SELECTEDVALUE('Table'[Ano])*100+SELECTEDVALUE('Table'[Mes])
return
CALCULATE(SUM('Table'[Volumen]),FILTER(ALLEXCEPT('Table','Table'[Ciudad],'Table'[Cod Trx]),'Table'[Ano]*100+'Table'[Mes]<=_anomes))

-Prom Gral = 
var _total=CALCULATE(SUM('Table'[Volumen]),ALL('Table'),GROUPBY('Table','Table'[Ciudad],'Table'[Cod Trx]))
var _count=CALCULATE(COUNTROWS('Table'),ALL('Table'),GROUPBY('Table','Table'[Ciudad],'Table'[Cod Trx]))
return
FORMAT(DIVIDE(_total,_count),"Fixed")

-Prom x Ano = 
var _total=CALCULATE(SUM('Table'[Volumen]),ALL('Table'),GROUPBY('Table','Table'[Ciudad],'Table'[Cod Trx],'Table'[Ano]))
var _count=CALCULATE(COUNTROWS('Table'),ALL('Table'),GROUPBY('Table','Table'[Ciudad],'Table'[Cod Trx],'Table'[Ano]))
return
FORMAT(DIVIDE(_total,_count),"Fixed")

 

Result:

See sample file attached bellow.

 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.