Average calculation

[Bharathi_99]

Hi all,

I need to do average monthly calculation in pbi

There was some problem with my dax, which is not giving correct average

I am doing count(matter_key) 

after that I am doing cumulative total for this and doing avearge using month number
Below is my dax

Cumulative Total =

CALCULATE(COUNT('WL Matter Extract'[MATTER_KEY]),

    FILTER (

        ALL ('Calendar Date'[Date]),

        'Calendar Date'[Date] <= MAX ( ('Calendar Date'[Date] ) )

    )

)

Average Test = DIVIDE([Cumulative Total],MAX('Calendar Date'[Month Number]),0)

I am facing issue when I have only one month dataa in a year
For example, I have Only feb month data in 2001 year then average for that year should be same as the count of matter key, 
But with my dax it's dividing with feb month number and giving avearge

Please help 🙂
Thanks in advance

[jdbuchanan71 · Accepted solution]

@Bharathi_99 

I rewrote it to use a couple measures.

1. Just the count of records.

Matter Key Count = COUNT( 'WL Matter Extract'[MATTER_KEY] )

2. A YTD running total of that count

Cummulative Count = 
CALCULATE ( [Matter Key Count], DATESYTD ( 'Calendar Date'[Date] ) )

3. A YTD active months count.  Only count months that have records in the 'WL Matter Extract' table

Cummulative Month Count = 
CALCULATE (
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Calendar Date'[Month Year] ),
            'WL Matter Extract'
        )
    ),
    DATESYTD ( 'Calendar Date'[Date] )
)

The Avg measure, where I only show the amount on months that have records to keep it from rolling forward to all future moths in the year.

Avg = 
VAR _Count = [Matter Key Count]
VAR _YTDCount = [Cummulative Count]
VAR _Months = [Cummulative Month Count]
RETURN DIVIDE ( _Count, _Count ) * DIVIDE ( _YTDCount, _Months )

I have attached my sample file for you to look at.

[Bharathi_99]

Hi @jdbuchanan71 

Thanks a lot

It worked  🙂

[jdbuchanan71]

@Bharathi_99 

Try it using AVERAGEX over your Calendar [Month Year] column. 

Average Test =
AVERAGEX (
    VALUES ( 'Calendar Date'[Month Year] ),
    CALCULATE ( COUNT ( 'WL Matter Extract'[MATTER_KEY] ) )
)

You need to have a month year where it is not just the month number (1, 2, 3, etc) but has the year also, Jan-2023, Feb-2023, so if you are looking at 14 months it averages 24 amounts.   If you use just 'Calendar Date'[Month Number] it would only average over 12 amounts

[Bharathi_99]

Hi @jdbuchanan71 
Thanks alot for replying

The below is example for output:
I want avg for each month

MONTH YEAR	Matter key count	AVG
Jan-23	2	2
Feb-23	4	(4+2)/2=3
Mar-23	6	(2+4+6)/3=4
Feb-94	2	2
Mar-94	4	(2+4)/2=3

Please reply back 🙂

[Tahreem24]

@Bharathi_99 Try this solution:

Step 1: Create a calculated column for Year

YearColumn = YEAR(CumulativeTable[MonthYear])

Step 2: Create a calculated column for Month

MonthColumn = MONTH(CumulativeTable[MonthYear])

Step 3: Create a cumulative count measure 

CumulativeCount = CALCULATE(SUM(CumulativeTable[Matter Key]),FILTER(ALL(CumulativeTable),CumulativeTable[MonthYear]<=MAX(CumulativeTable[MonthYear]) && CumulativeTable[YearColumn]=MAX(CumulativeTable[YearColumn])))

Step 4: Then create a MonthCount meaure

MonthCount = CALCULATE(SUM(CumulativeTable[MonthColumn]),FILTER(CumulativeTable,CumulativeTable[MonthYear]<=MAX

(CumulativeTable[MonthYear])))

Step 5: And the last and final step to divide step3 and step4

Final Answer = DIVIDE(CumulativeTable[CumulativeCount],[MonthCount])

[Bharathi_99]

Hi,
Thanks foe replying
For feb there is no before month right, so avearge will be same as count might be, it should be like sum/total num of months 
If only feb is there then 2/1
For march feb+march/2

Cumulative Total = CALCULATE(

    COUNT('WL Matter Extract'[MATTER_KEY]),

    FILTER (

        ALL ('Calendar Date'),

        'Calendar Date'[Date] <= MAX('Calendar Date'[Date])

    )

)

Average Test = DIVIDE([Cumulative Total],MAX('Calendar Date'[Month Number]),0)


This is my exact dax for showing avearge

Can you please let me know what changes I should do in my dax...

Please reply back, Thanks a lot