Measure to calculate total column sum by filtered value / category

[wanwong]

Greetings Power Bi experts,

Is there a specific measure or formula that i can use to calculate total booked, tentative and availability per dedicated storefronts and sub storefronts by month? I have an example of a data set below which you can use to compute. Note that i also need this formula to be able to work for long term use in the coming months and years and if there is something that i can do to improve my current data, any suggestions are welcomed. 

FYI the availability table is extracted from Table B whereas Booked and Tentative are both from Table A. So they don't belong in the same sheet

Based on the given figures, I want to know the total sum of tentative, booked and available the staffs (from the column storefront, sub storefront and by country if possible) per month using the column "Month_Year". And in Power Bi Desktop view, I'd expect the values would change based on the selected slicers.

New data is being imported on a weekly basis so we will have new months (October2022, November2022, December2022, etc) and i was thinking if the measure can be applied to future months as well?

Staff Name	Country	Storefront	Sub Storefront	Position	Engagement Name	Month_Year	Tentative	Booked	Availability
Bob	SG	Accounting & Internal Controls	Internal Audit	Senior Manager	Project A	June2022	0	1.6	6.4
Bob	SG	Accounting & Internal Controls	Internal Audit	Senior Manager	Project B	June2022	0	1.6	6.4
James	MY	Cyber & Strategic Risk	Cyber	Assistant Manager	Project A	June2022	1.6	4	4
James	MY	Cyber & Strategic Risk	Cyber	Assistant Manager	Project B	June2022	1.6	4	4
Eric	TH	Regulatory & Legal Support	Financial Crime	Consultant	Project A	June2022	1.6	6	2
Eric	TH	Regulatory & Legal Support	Financial Crime	Consultant	Project B	June2022	1.6	6	2
Bob	SG	Accounting & Internal Controls	Internal Audit	Senior Manager	Project C	July2022	1.2	4	4
Bob	SG	Accounting & Internal Controls	Internal Audit	Senior Manager	Project D	July2022	1.2	4	4
James	MY	Cyber & Strategic Risk	Cyber	Assistant Manager	Project C	July2022	0	3	5
James	MY	Cyber & Strategic Risk	Cyber	Assistant Manager	Project D	July2022	0	3	5
Eric	TH	Regulatory & Legal Support	Financial Crime	Consultant	Project C	July2022	0	2	6
Eric	TH	Regulatory & Legal Support	Financial Crime	Consultant	Project D	July2022	0	2	6

[Greg_Deckler]

@wanwong What are the expected results from that sample data?

[wanwong]

here's a sample of the expected results:

i'd expect the values to change if i hover on the bar charts (currently is showing the total sum of the whole column)

let me know if this info is sufficient 😄