Calculate the count of measure results and show results in designated columns

[Jbridgewater]

So I need to calculate the count of a measure.  I have calculated the number of vacancies as: 

Vacancy = CALCULATE('Capacity Census'[Total Capacity] - 'Capacity Census'[Total Census])

I need to show a count of Vacancy by numeric categories.

Below is the example in a mock up from Excel.  Using the Total capacity bucket of 2 and the # of Vacancies of 2 the matrix shows a count of 34 in the # of vacancies 1 "bucket" and a count of 6 in the # of vacancies 2  bucket.  This means that there are 34 instances of 1 vacancy and 6 instances of 2 vacancies for a total of 46 vacancies.

I have created the Total capacity buckets in a seperate table and linked the tables via the alias number.  I think I need the # Vacancies to also be "bucketed" but as a count.

	# Vacancies
Total Capacity	1	2	3	4	5	6	7	8	Grand Total
1	5								5
2	34	6							40
3	188	23	5						216

Below are the tables I am using: first is the census/capacity table. Second is the Capacity Buckets table

Long Alias	Date	Actual/Forecast	Census	Alias	Capacity
059204 MO-Mntr-4407 Sussex Dr-ISL_WGH	12/1/2024	Actual		59204	1
059204 MO-Mntr-4407 Sussex Dr-ISL_WGH	12/1/2024	Actual	1	59204
059207 - MO-Mntr-1704 Affirmed Blvd -ISL	12/1/2024	Actual		59207	2
059207 - MO-Mntr-1704 Affirmed Blvd -ISL	12/1/2024	Actual	2	59207
059208_5465 Geyser Blvd_WGH	12/1/2024	Actual		59208	3
059208_5465 Geyser Blvd_WGH	12/1/2024	Actual	2	59208
059210_3516 Valencia Dr_WGH	12/1/2024	Actual		59210	2
059210_3516 Valencia Dr_WGH	12/1/2024	Actual	2	59210
059212_1621 Hanover Blvd_WGH	12/1/2024	Actual		59212	2
059212_1621 Hanover Blvd_WGH	12/1/2024	Actual	2	59212

Alias	Long Alias	Date	Capacity Bucket
059204	059204 MO-Mntr-4407 Sussex Dr-ISL_WGH	12/1/2024	1
059207	059207 - MO-Mntr-1704 Affirmed Blvd -ISL	12/1/2024	2
059208	059208_5465 Geyser Blvd_WGH	12/1/2024	3
059210	059210_3516 Valencia Dr_WGH	12/1/2024	2
059212	059212_1621 Hanover Blvd_WGH	12/1/2024	2
059213	059213 MO-Mntr-3709 Rollins Rd-ISL_WGH	12/1/2024	2

Here are the table connections

I hope this is clear.  Thanks for any assistance.

John

[Anonymous]

Hi @Jbridgewater ,

Did @danextian  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[Jbridgewater]

Thanks to both.  I will need to digest this a bit.

John

[danextian · Accepted solution]

Hi @Jbridgewater 

As @lbendlin, you cannot bucketized measures. They dont have filter context and they need to be materialized first.  I've attached several sample pbix files that does this.

[lbendlin]

Power BI does not support dynamic bucketing. You need to bring your own bucket list (apologies for the pun).

You cannot measure a measure directly. Materialize it first.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.