cancel
Showing results for
Did you mean:
Frequent Visitor

## Percentage of Ratings For each Year

Hi, i am trying to show the ratings per year with a bar chart but can find the % of grand total. I want to show

2019

Rating 5 - 10%

Rating 4 - 20%

Rating 3 - 60%

Rating 2 - 5%

Rating 1 - 5%

Same for other years but I can only get the grand total %.

1 ACCEPTED SOLUTION
Frequent Visitor

Good evening, I figured this out using three measures:

#1 -

Annual Count =
CALCULATE (
COUNTROWS ( 'Consolidated 5 year' ),
ALLSELECTED ( 'Consolidated 5 year' ),
VALUES ( 'Consolidated 5 year'[YEAR] )
)
#2 -
APFR Count =
CALCULATE (
COUNTROWS ( 'Consolidated 5 year' ),
ALLSELECTED ( 'Consolidated 5 year'[APfR_BusObj] ),
VALUES ( 'Consolidated 5 year'[APfR_BusObj])
)

#3 -
Annual % = CALCULATE(DIVIDE([APFR Count],[Annual Count]))

For #3 I had to change the Format to be in %s to show properly in the chart.

9 REPLIES 9
Super User III

Hi,

The measure should be something like this

=calculate([measure],allexcept(calendar,calendar[year]))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Hi, would this also be 5 measures as a previous member indicated? I am very new to creating measures so I am struggling a bit to understand the concept.

Super User III

It should be just one measure.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

This is what I inputted as one measure:

Per Year APfR = calculate(COUNTROWS('Consolidated 5 year'),allexcept('Consolidated 5 year','Consolidated 5 year'[YEAR]))

But i am getting a full count of the population in specific year and not based on the rating the employees were given. Each row could have the number 1, 2, 3, 4, or 5 in it. I need each of these subtotalled and then divided by the count for the specific year to get the % for the year.

Super User III

Hi,

Share a simple dataset and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Good evening, I figured this out using three measures:

#1 -

Annual Count =
CALCULATE (
COUNTROWS ( 'Consolidated 5 year' ),
ALLSELECTED ( 'Consolidated 5 year' ),
VALUES ( 'Consolidated 5 year'[YEAR] )
)
#2 -
APFR Count =
CALCULATE (
COUNTROWS ( 'Consolidated 5 year' ),
ALLSELECTED ( 'Consolidated 5 year'[APfR_BusObj] ),
VALUES ( 'Consolidated 5 year'[APfR_BusObj])
)

#3 -
Annual % = CALCULATE(DIVIDE([APFR Count],[Annual Count]))

For #3 I had to change the Format to be in %s to show properly in the chart.

Super User II

I am not clear about your question. Are you looking for this?

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

@ryan_mayuI am trying to show the percentage within the year. Each year would add to 100% rather than the percentage presented across the entire population. So the percentage would be isolated to each subset.

Super User II

Did you create 5 measures ? If they are measures, they will be isolated in the each year.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.