Tables - incomplete months - calculation issues

[RobinIrving]

Hello, 

Ive done lots of searching and used copilot a lot but still cant quite get there. Apologies for the long post but its not easy to explain. 

I am pulling in a table of forecast hours for resources. Each one gets split by month and each month is assigned a row each. So if Joe Bloggs is allocated 0.2 FTE per month from January to June - he has 6 rows in the report. Etc.. He however will NOT have rows for July to December showing as 0.00.

To make it a bit more complex some resources have allocations to more than one project for each month. So some might have 2 or 3 rows for January etc...

Im trying to create a matrix with some calculations showing the sum of their hours per month - and then what their availability is based on that. Sounds simple but....

Due to the multiple months columns I have used the below and a unique count column to sort out this and get the correct number back. This works fine for all resources however anyone with no rows for a month still show up as blank in my matrix. I appreciate this is because the row technically doesnt exist in the table ?

Available =

IF(

    ISBLANK(RM_MERGED_ALL_DEMAND[FTE]),

    1,

    SWITCH(

        TRUE(),

        RM_MERGED_ALL_DEMAND[UniqueCount] = 1, 1 - RM_MERGED_ALL_DEMAND[TotalAllocationWithZero]+0,

        RM_MERGED_ALL_DEMAND[UniqueCount] = 2, 0.5 - RM_MERGED_ALL_DEMAND[TotalAllocationWithZero]+0,

        RM_MERGED_ALL_DEMAND[UniqueCount] = 3, 0.333 - RM_MERGED_ALL_DEMAND[TotalAllocationWithZero]+0,

        RM_MERGED_ALL_DEMAND[UniqueCount] = 4, 0.25 - RM_MERGED_ALL_DEMAND[TotalAllocationWithZero]+0,

        RM_MERGED_ALL_DEMAND[UniqueCount] = 5, 0.2 - RM_MERGED_ALL_DEMAND[TotalAllocationWithZero]+0,

        RM_MERGED_ALL_DEMAND[UniqueCount] = 6, 0.167 - RM_MERGED_ALL_DEMAND[TotalAllocationWithZero]+0,

        RM_MERGED_ALL_DEMAND[UniqueCount] = 7, 0.143 - RM_MERGED_ALL_DEMAND[TotalAllocationWithZero]+0,

        RM_MERGED_ALL_DEMAND[UniqueCount] = 8, 0.125 - RM_MERGED_ALL_DEMAND[TotalAllocationWithZero]+0,

        1 - RM_MERGED_ALL_DEMAND[FTE]  -- Default case if UniqueCount is outside 1-8

    )

)

So I have used another formula to create a measure and also to use the COALESCE function to replace the blanks in the matrix. This works and they now show up as 0.00 rather than blanks in the matrix. My calculation column is however still now working. 

TotalAllocationWithZero = COALESCE([TotalAllocation], 0)

I guess this is due to the fact one is in a column in the table and the measure is outside that ?  

Just trying think of a way to do this and its been tricky - any help much appreciated. Sorry if confusing !!

[RobinIrving]

Hello,

Tried copilot but cant quite get it right unfortunately.

So Ive got the 'dummy rows' in place. Thought they were working fine however my removing duplicates does not get rid of them all. So in summary. 

For each resource they have multiple rows. Some have two or more rows for one month. Most have one. On top of this I have a dummy month row for each resource now in place in my merged queries. That is all working fine. 

I just need to do the following. 

If any of the dummy rows already have a 'real' row in place for the same month - I want to remove them. If any do not have a real row in place for the same month - I want to keep them. FTE column shows the hours I am interested in. All the real rows have an actual number in them. For all the dummy rows I have entered 0 for these. 

Columns in question i need to use:

User:

MergedMonthNames:

FTE:

Table example below of what I have now and what I want to to result in. For the table there will be multiple resources that this should work for. Any help would be amazing !! Dummy rows are in red.

User	MergedMonthNames	FTE
David	January	0.5
David	January	0.5
David	February	1
David	March	1
David	April	1
David	January	0
David	February	0
David	March	0
David	April	0
David	May	0
David	June	0
David	July	0
David	August	0
David	September	0
David	October	0
David	November	0
David	December	0

User	MergedMonthNames	FTE
David	January	0.5
David	January	0.5
David	February	1
David	March	1
David	April	1
David	May	0
David	June	0
David	July	0
David	August	0
David	September	0
David	October	0
David	November	0
David	December	0

[RobinIrving]

@Anonymous  @danextian 

Hello both. Sorry I should have tagged you in this. Apologies Im new to here !! You have ben very helpful to date. I just need to work out the above and then will finally be sorted !!

Much appreciated. 

Robin

[Anonymous]

Hi @RobinIrving ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[danextian]

Hi @RobinIrving 

It would be easiser for us to understand what you're trying to achieve if you provided a workable sample data (not an image), your expected result from that same data and the reasoning behind. You may post a link to a sanitized copy your pbix or an excel file stored in the cloud.

That aside, here's the clude why they don't appear even if you add zero or use colesce

This works fine for all resources however anyone with no rows for a month still show up as blank in my matrix

because you can't assign a value to a row that doesn't exist.  So if employee A has data only unitl June and you use the same column, employee A will show data only until June. This is one of the reasons why we use a separate dimensions table containing a complete set of dates (even the future ones) so values can be assigned to them. Otherwise, you will have to append some dummy future dated rows for each employee into your data just to make up for the missing ones which is another way to slow down data refresh.

[RobinIrving]

Thanks for mentioning the dummy option - I managed to get that working well with a bit of fiddling. With this level of data it works fine.If I ever need anything more I will worl out a way to be able to provide a cleansed PBIX for you all to look at. Cheers

[Anonymous]

Hi @RobinIrving,

In fact, You only need to remove the sensitive data from the table records or tried to replace them to dummy values.(e.g. abc, test123)
Regards,

Xiaoxin Sheng

[Anonymous]

Hi @RobinIrving,

Any update on this? 

Regards,

Xiaoxin Sheng

[RobinIrving]

Hello, 

Yes I decided to go with the dummy rows for each month as a solution. Still got a few issues with this as some of the resources have multiple rows for themselves and also there are quite a lot of them with issues swith the way they have been set up as well. Rather comlicated but geting there. 

Happy to class the creating duplicate month rows as a solution. 

Cheers

[RobinIrving]

Thanks both. The problem is this is for my work and they dont allow me to mail to my own email so not sure how I could do this even if I cleanse the data prior to doing so. I dont have Desktop for personal use only at work so I cant create a 'dummy' PBIX either easily.  I think I might use the add dummy appended rows and I am working on that now. My data is not huge less than 1000 rows and its only refreshed a few times per week - so adding a few seconds onto that is not something that will be an issue. If that doesnt work I shall come back and work out how to create some dummy data to show exactly what I am trying to do. 

Sorry its rather complex to explain and yes totally understand why a cleanes PBIX would make any question like this much easier to solve. Thanks all anyway !!