Average of COUNTROW

[admin_xlsior]

Hi,

How to allocate some value (lets say Yearly Target), equally thorugh Quarters or Months ?

Firstly, I have my Target Table:

Year	Target
2019	670

 Then I have my Measures to allocate this like below:

Target Value = SUMX(

VALUES('Date'[Year]),

COUNTROWS('Date') / CALCULATE(COUNTROWS('Date'),ALLEXCEPT('Date', 'Date'[Year])) * CALCULATE( SUM(ProductivityTarget[Target]), FILTER(ALL(ProductivityTarget[Year]), ProductivityTarget[Year] = 'Date'[Year])))

Because it actually counting days of my Date table, it will gave me like this :

As picture above, the Value is not the same because they count the days per quarters which is not the same. This is also happening if add Months :

I am thinking about to Average it, but I don know where to start and how. Or whether it is the correct way to have a fixed allocation factor ?

Thanks in advance.

[TeigeGao · Accepted solution]

Hi admin_xlsior,

According to your description, my understanding is that you want to get the data divided averagely by the months, in your scenario, the data should be 55.8 for all months.

We can first create three calculated columns on the Date table using the following query:

Month = CONCATENATE( FORMAT('Date'[Date],"MMMM")," " &FORMAT('Date'[Date],"YYYY"))

Quarter = "Quarter" &" "& FORMAT('Date'[Date],"Q")&","&'Date'[Date].[Year]

Monthnumber = MONTH('Date'[Date])

Then sort the Month column by Monthnumber:

After that, we can create a measure like below:

Target Value = DIVIDE(SUM(ProductivityTarget[Target]),12)*CALCULATE(DISTINCTCOUNT('Date'[Month]),FILTER('Date','Date'[Year] = MIN(ProductivityTarget[Year])))

Drag the Month, Quarter and Target Value column to table visual, the result will like below:

Please refer to the following pbix file

https://1drv.ms/u/s!Ao9Of0JgO6MU7WB5_jwWjrCQCQYw  

Best Regards,

Teige