Measure return 1 if year, 4 if quarter...

[Anonymous]

I need to calculate annualized revenue per full time employee and I'm having trouble getting the integar based on the time frame. I'm trying to write a measure so that I get a 1 if it's grouped by year, 4 if by quarter, and 12 if by month. I have attached a visual of what I hope the output will look like.

Thanks.

[amitchandak · Accepted solution]

Try something like this

 Annualized Revenue per FTE =
 var Annualization =if(ISFILTERED('Date'[QTR Year]),4,12)
 return
  DIVIDE([Total Revenue], [FTEs]) * Annualization

Also refer: https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.
Proud to be a Datanaut My Recent Blog -Time Intelligence - Direct Query

[Anonymous]

Let's say I'm tying to calculate annualized revenue per full time eployee on a quarterly basis. I'd use:

Total Revenue = SUM('Revenue'[Revenue])
FTEs = SUM('HR'[Full Time Employees])

Annualization = ? (the measure I'm trying to figure out)

Where the final measure looks like:

Annualized Revenue per FTE = DIVIDE([Total Revenue], [FTEs]) * Annualization

There is a relationship between the dates in the Revenue and HR table to my calendar table so when I group it by quarter, those numbers will adjust accordingly. What I need is for the annualization measure to adjust as well so that is returns 4 if grouped by quarter and 12 if it's by month. I hope that helps. Thanks!

[amitchandak · Accepted solution]

Try something like this

 Annualized Revenue per FTE =
 var Annualization =if(ISFILTERED('Date'[QTR Year]),4,12)
 return
  DIVIDE([Total Revenue], [FTEs]) * Annualization

Also refer: https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

That's very close! The only issue is that if I expand all levels it returns a 4 for month and quarter.

[amitchandak]

Hmm. Let us give preference and test. You can try the same with if.

Switch(true(),
ISFILTERED(Table[Month-Year]),12,
ISFILTERED(Table[Qtr-Year]),4,
1
)

[Anonymous · Accepted solution]

I got it! Made a sligt tweak to your initial measure. Thanks so much!

Annualization = 
IF(ISINSCOPE('Date Table'[MonthInCalendar]), 12,
    IF(ISINSCOPE('Date Table'[QuarterInCalendar]),4,
        IF(ISINSCOPE('Date Table'[Year]), 1, BLANK()
        )
    )
)