Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Measure to calculate total column sum by filtered value / category

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 NameCountryStorefrontSub StorefrontPositionEngagement NameMonth_YearTentativeBookedAvailability
BobSGAccounting & Internal ControlsInternal AuditSenior ManagerProject AJune202201.66.4
BobSGAccounting & Internal ControlsInternal AuditSenior ManagerProject BJune202201.66.4
JamesMYCyber & Strategic RiskCyberAssistant ManagerProject AJune20221.644
JamesMYCyber & Strategic RiskCyberAssistant ManagerProject BJune20221.644
EricTHRegulatory & Legal SupportFinancial CrimeConsultantProject AJune20221.662
EricTHRegulatory & Legal SupportFinancial CrimeConsultantProject BJune20221.662
BobSGAccounting & Internal ControlsInternal AuditSenior ManagerProject CJuly20221.244
BobSGAccounting & Internal ControlsInternal AuditSenior ManagerProject DJuly20221.244
JamesMYCyber & Strategic RiskCyberAssistant ManagerProject CJuly2022035
JamesMYCyber & Strategic RiskCyberAssistant ManagerProject DJuly2022035
EricTHRegulatory & Legal SupportFinancial CrimeConsultantProject CJuly2022026
EricTHRegulatory & Legal SupportFinancial CrimeConsultantProject DJuly2022026
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 😄


Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.