cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AXJKATBI Frequent Visitor
Frequent Visitor

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

Accepted Solutions
natelpeterson New Contributor
New Contributor

Re: Calculating percentage with conditions and grouping

@AXJKATBI  - 

Would something like this work?:

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

Cheers!

Nathan

 

2 REPLIES 2
natelpeterson New Contributor
New Contributor

Re: Calculating percentage with conditions and grouping

@AXJKATBI  - 

Would something like this work?:

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

Cheers!

Nathan

 

AXJKATBI Frequent Visitor
Frequent Visitor

Re: Calculating percentage with conditions and grouping

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