Percentage Measure on Card Visual - Incorrect Calculation across Dates

[Anonymous]

Does anyone know how I could get a card visual to change dynamically based on the year and month selected on two slicers?

For my card visual, I'm currently using this formula: 

Occupancy % = 1 - (DIVIDE([Vacancies],[Total Units])), where my [Vacancies] and [Total Units] are two separate measures.

• Vacancies = CALCULATE(COUNTROWS(TenantsTable),TenantsTable[Status]="Vacant")
• Total Units = SUM(HomesTable[Total Units])
  
  

The problem here is that the occupancy % calculates correctly when I select one year and one month (e.g. September 2017); however, when I select "All" months and only one year (or when all years and all months are selected), the % calculation is wrong. 

Is there something I should be taking the average of? Or is there any other way to make the measure dynamic across all slicers selected?

I think it might be an easy answer, but something I just can't seem to figure out. Many thanks in advance for reading this!

[mahoneypat · Accepted solution]

Please try this expression in your card.  It makes a virtual table for each year/month combination in context and the occupancy for each, and then averages all those values.  Replace with your actual table/column names.

NewMeasure = AVERAGEX(SUMMARIZE(Date, Date[Year], Date[Month]), [Occupancy %])

Regards,

Pat

[mahoneypat · Accepted solution]

That measure shouldn't include blanks as zeros, but since your occupancy measure is 1 - every row will have a value.  To fix it, I would modify that measure.

Occupancy % = IF(NOT(ISBLANK([Vacancies])), 1 - (DIVIDE([Vacancies],[Total Units])))

I assume you have a simple measure for Vacancies so not critical to break it out into a variable first for performance.

Regards,

Pat

[Anonymous]

@mahoneypat You are the best!! Thank you so much for that fix - the card is perfect now! 🙂 

[mahoneypat · Accepted solution]

Please try this expression in your card.  It makes a virtual table for each year/month combination in context and the occupancy for each, and then averages all those values.  Replace with your actual table/column names.

NewMeasure = AVERAGEX(SUMMARIZE(Date, Date[Year], Date[Month]), [Occupancy %])

Regards,

Pat

[Anonymous]

Hi @mahoneypat ! Thank you for your quick response!

That new measure you suggested works really well. I have one follow up question - the AVEREAGEX measure you suggested gives me the correct results for years in which I have data for all 12 months. However, for 2020, I only have data for months 1 to 9. In this case, when I select the year slicer to be 2020 and month as "All," the card visual returns 92.67% as my occupancy %, whereas the answer should be 90.23%. 

Any idea how I could deal with those blank months?