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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating percentage with conditions and grouping

I have searched for similar topics but none of the solutions exactly match my needs, so I need some help on calculating percentage with conditions and grouping, plus the average percentage for the entire company. A simple sample data table looks like this:

 

ValuesTeamsIndustriesChargeableActiveMonth
200AInd_01YY3
150CInd_05YY2
64CInd_03YY1
251BInd_01YY3
20AInd_01NY1
18BInd_04NN1
30CInd_03NN2

 

So I would need to calculate the percetage of chargeable values per team over a period of time (say month), with the possibility to further drill down by industry, by Active type (y/n), also the entire company average (not just per team) per industry and other parameters.

 

I would need average per quarter/year, and moving average too. But I know how to do this with a proper time/calendar table. So getting the actual conditional percentage calculated correctly is the main problem. Any help and suggestion is appreciated. Thanks.

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@Anonymous  - 

Would something like this work?:

Chargeable % = 
DIVIDE(
    CALCULATE(SUM(Table1[Values]),Table1[Chargeable] = "Y"),
    CALCULATE(SUM(Table1[Values]),ALL(Table1[Chargeable]))
)
+0

Cheers!

Nathan

 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@Anonymous  - 

Would something like this work?:

Chargeable % = 
DIVIDE(
    CALCULATE(SUM(Table1[Values]),Table1[Chargeable] = "Y"),
    CALCULATE(SUM(Table1[Values]),ALL(Table1[Chargeable]))
)
+0

Cheers!

Nathan

 

Anonymous
Not applicable

Hey, Nathan

 

It works! Thanks for your amazingly simple but super-effective formula. I can't thank you enough. Much appreciated.

 

All the best

 

Alex

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.