Avalable Resource Capacity

DAX DAX Calculations
1

I work in a professional services organization and am trying to build a PBI report that shows associates who have available capacity on any given week based on their targets and schedules. I have the report working all the way up until the grand totals all the way on the right. I know I can turn off this value but it actually would be really helpful have if we can make this work.

Because I’m having to return the MIN at the week level, the total column is not correct. I was able to get the weekly totals to add up using SUMMARIZE, but have not been able to solve for the Grand Total on the right.

I have attached an example PBIX file hoping this might help with troubleshooting.

Thanks for any help,

JR

Capacity Totals
Capacity Sample Data.pbix (788.8 KB)

2

@james8992
Try the following measure:

Avalable Capacity Summarize =
    VAR vTable =
        CALCULATETABLE (
            ADDCOLUMNS (
                CROSSJOIN (
                    DISTINCT ( VALUES ( Consulting_Headcount[Associates] ) ),
                    DISTINCT ( VALUES ( 'Date'[FW StartOfWeek] ) )
                ),
                "@Capacity Summarize", [Avalable Capacity]
            )
        )
    RETURN
        SUMX ( vtable, [@Capacity Summarize] )

2
2809×615 14.1 KB

Capacity Sample Data.pbix (789.4 KB)

Let us know if this works.

Thanks.

1 Like
3

@MudassirAli Thank you so much, this fixed the issue I was trying to solve. The complexity of the formula you provided reminds me I have a long way to go on my journey.

Thanks again.

4

@james8992 You don’t have a long way to go. You were almost there with your calculation. The reason your measure didn’t work was because you were filtering with FW Start Of Week on the columns but didn’t give FW Start Of Week context in your measure. What Crossjoin does is it combines columns/tables as:

1
1686×817 36.9 KB

Here we joined the columns and forced the measure to sum the values keeping the FW Start Of Week Filter Context too.

What I would recommend you is to start using DAX Studio to analyze your code especially virtual tables. This way you will learn way faster.