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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Datorz
Regular Visitor

Group Records by Month then calculate Percent

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...

 

PowerBI.png

 

Thanks in advance!

 

1 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:

44.PNG

 

Thanks,
Xi Jin.

View solution in original post

5 REPLIES 5
v-xjiin-msft
Solution Sage
Solution Sage

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.

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:

current.png

 

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

 

future.png

 

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

 

Thanks!

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:

44.PNG

 

Thanks,
Xi Jin.

Anonymous
Not applicable

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). 

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!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors