Group Records by Month then calculate Percent

[Datorz]

Good Day,

I attempted to review similar issues but they seemed more complex than what I am attempting to do.

I would like to group my records by month, then calculate the % of each of the 4 RecordType's so that each month totals 100%.

For example, the %GT sums the records of all three months and calcualtes the percent per record against the grand total.  So adding up all the numbers below = 100%.  I would like to group my records by month and then calculate the percent based on the Month Total, not the grand total so that each month equals 100%. 

Is there a way to group them in the Fields area or do I need to add a new column to my table to do this somehow?

Of course this works great if I filter the data for only one month...

Thanks in advance!

[v-xjiin-msft]

Hi @Datorz,

It seems feasible. However please share us your source table structure and some sample data with its corresponding expected result. Otherwise we don't know where to start.

Thanks,

Xi Jin.

[Datorz]

Thank you for the reply!
I put a quick example together since I couldn't share the other data.

Here is a copy of the Excel data as CSV:

Resource Name,Month,RecordType,Actual(h)
Person A,1/1/2018,Project,100
Person A,1/1/2018,Support,20
Person A,1/1/2018,Training,50
Person A,1/1/2018,Unavailable,30
Person A,2/1/2018,Project,80
Person A,2/1/2018,Support,50
Person A,2/1/2018,Training,20
Person A,2/1/2018,Unavailable,50
Person A,3/1/2018,Project,150
Person A,3/1/2018,Support,10
Person A,3/1/2018,Training,20
Person A,3/1/2018,Unavailable,20
Person B,1/1/2018,Project,50
Person B,1/1/2018,Support,100
Person B,1/1/2018,Training,45
Person B,1/1/2018,Unavailable,5
Person B,2/1/2018,Project,90
Person B,2/1/2018,Support,80
Person B,2/1/2018,Training,10
Person B,2/1/2018,Unavailable,20
Person B,3/1/2018,Project,180
Person B,3/1/2018,Support,5
Person B,3/1/2018,Training,10
Person B,3/1/2018,Unavailable,5

It shows up like this based on my current settings:

But if I were to mock-up what I want, this would be it:

I hope that helps explain what I'm tring to do, if not please let me know.

Thanks!

[v-xjiin-msft · Accepted solution]

Hi @Datorz,

Did you mean something like this?

Create a new Measure:

Percentage value =
CALCULATE (
    SUM ( Sheet1[Actual(h)] ),
    ALLEXCEPT ( Sheet1, Sheet1[Month].[Month], Sheet1[RecordType] )
)
    / CALCULATE (
        SUM ( Sheet1[Actual(h)] ),
        ALLEXCEPT ( Sheet1, Sheet1[Month].[Month] )
    )

Put this measure in Values:

Thanks,
Xi Jin.

[Anonymous]

This worked really well for what I needed as well, but I have one issue left in that this graph no longer responds slicer or visual filters. Is there a way to get this to still filter with the slicer input? I have 3 locations (simple branch 1, branch 2, and branch 3). 

[Datorz]

Thank you Xi Jin!

I was playing with that formula that I saw on other similar posts, but I didn't use RecordType in there for some reason.

Once I changed the Format to percentage I got it to work exactly like you presented, appreciate the assistance!