cancel
Showing results for
Did you mean:
Highlighted
AXJKATBI 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:

 Values Teams Industries Chargeable Active Month 200 A Ind_01 Y Y 3 150 C Ind_05 Y Y 2 64 C Ind_03 Y Y 1 251 B Ind_01 Y Y 3 20 A Ind_01 N Y 1 18 B Ind_04 N N 1 30 C Ind_03 N N 2

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

Re: Calculating percentage with conditions and grouping

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

Re: Calculating percentage with conditions and grouping

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

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