Forum Discussion
How Do I Calculate Total Capacity for Daily Visit Chart?
Hi,
Fairly new PBI user and have only just started learning DAX so am very stuck on this question. I've tried searching but couldn't find a similar post.
I have 3 tables:
- A Visit table that has the first and last card swipe of every employee who visits each office for each day
- Date table
- Office table which contains a few columns, the most important being the number of desks and employees for each office
I'm trying to produce a bar and line combo chart showing the number of unique visits each day and a constant line on the y axis showing the total capacity (total number of desks) for the filtered results in the chart (users can filter by date range, office, region).
My issue is when I try to do this, the total number of desks changes for each day as not all offices and regions have staff turning up so aren't included in that day's total. When I look at this as a table, the total across the period is correct (2115) and when I put in a card visual this shows the correct value too. It's only when the data is shown on a daily basis that I have an issue.
I've tried creating a few different measures using ALLEXCEPT and REMOVEFILTERS but I can't get it to work. Anyone have any tips or advice for me please?
For anyone interested, I solved my issue by simply changing the chart to a bar chart and adding a constant line equal to the desk count. I placed a card visual that summed the number of desks on the report page and the figures matched no matter the time period I selected.
3 Replies
- Greg_Deckler
Community Champion
average_joe
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365Thanks for the info Greg_Deckler . Perhaps I don't quite understand the functions but I can't figure out how that would work for me.
If someone is selecting a date range in a slicer on the report, I don't understand how I can incorporate this in the tmpTable created in the examples you sent through.
I've tried CALCULATE(SUM([DeskCount]), ALLEXCEPT('OfficeData','OfficeData[OfficeName])) but again, as soon as I place this on the chart then I'm seeing a variable line which is affected by whether or not staff from an office turn up to work that day.
I'm really stuck on this one.
For anyone interested, I solved my issue by simply changing the chart to a bar chart and adding a constant line equal to the desk count. I placed a card visual that summed the number of desks on the report page and the figures matched no matter the time period I selected.
