Calculating Percentage of Quota used

[Mattula]

Hi All 

This is probably something very simple but I am new to PowerBi and I am really struggling to make it happen. 

I have a table which has quotas per team 

Quota PropertiesClip Quota

Team A	30000
Team B	90000
Team C	32000
Team D	60000
Team E	22000
Total Quota	234000

Another table (UniqueClips) with all the clip data, one of the columns of this data is user group. The unique values of the user group detailed below.

User Group

Team A - soccer - can

Team A - Soccer - de

Team A – Soccer - en

Team A – Soccer - es

Team A - Soccer - it

Team A – Soccer - jp

Team A - Social - DACH

Team A - Social - Spain

Team A-  Social -Japan

Team A Germany

Team A

Team B

Team A Internal

Team C

Team E

Team D

Team D Social

How would I calculate all the team values from the unique clips table and then comapre them to the quotas in the quota table. 

The end goal would be to show 

TEAM A = X% of the Quota and so on 

Any help greatly appreciated 

[v-henryk-mstf · Accepted solution]

Hi @Mattula ,

You can separate out the UniqueClips field in the power query and then create a relationship between the two tables. Then use the following formula to count the percentage of attributes:

1 =
VAR count_group =
    COUNTROWS ( table2 )
VAR Amount =
    CALCULATE ( COUNTROWS ( Table2 ), ALL ( Table2 ) )
RETURN
    count_group / Amount

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mattula]

Thanks for the response. 

But I am not really following what I need to do here. In the meantime I have calculated the individual teams total clip usage. I just need to divide it against the quota but have been unable to achieve this.

[lbendlin]

For each "row" in your source data you can grab the "current"  value of the quota for the team in the filter context.  Then you grab the same quota value, but you remove the team filter, for example with ALLSELECTED().  Then divide one by the other.