Percent filter question

[pbrainard]

I'm calculating occupancy for a particular fiscal year, which seems to be working fine (except the total, I'd like that to be the average percentage of the total - right now it's summing).

The issue I run into is when I select multiple fiscal years. It's adding the percentages. How can I write this formula to give me a percentage over one or more fiscal years. I am using a date table.

Occupancy = DIVIDE([cntType], [psbBednights],0)

[v-angzheng-msft · Accepted solution]

Hi, @pbrainard 

Try to add two additional measures, one to calculate the percentage of each year, and the other to modify the sum of the total to the average.

Sample data:

_divide:

_divide = 
var _a=SUM('Table'[Sale])
var _b=CALCULATE(SUM('Table'[Sale]),ALL('Table'))
var _divide=DIVIDE(_a,_b)
return _divide

So to create 2 measures like this:

_Year% = CALCULATE([_divide],ALLEXCEPT('Table','Table'[Year]))

The result we want would be this:

_average% =
IF ( HASONEVALUE ( 'Table'[Year] ), [_divide], AVERAGEX ( 'Table', [_Year%] ) )

 result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@pbrainard , this should work fine as long as these are sum or count measure

example

divide(sum(Table[A]), sum(Table[B]))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.