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

Divide Row Value with a measure

Hi everyone,

 

I have a survey which can be completed by several people from the same company. I want the average score per category and this is done by taking the value in the score column and dividing it by the number of people who filled out the survey from the company. 

 

However, the company participant count is a measure which has been calculated using the following:

 

COUNTROWS(FILTER('Survey Responses','Survey Responses'[Company Name] IN VALUES('Section_Summary'[Company Name])))
 
as number of people from a particular company is located in a different table.
 

I have the following table,

 

 

CompanyScoreCategoryCompany Participant Count
ABC129Cat13
ABC103Cat23
XYZ36Cat11
XYZ26Cat21

 

 
Ideally I want to add another column (measure or calculated column otherwise) that takes the value in the score column and divides it by the value calculated using the company participant count measure?
 
Thank you in advance!
1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

Here is a measure expression you can try

 

Avg for Company and Category =
CALCULATE (
    AVERAGE ( Table[Score] ),
    ALLEXCEPT (
        Table,
        Table[Company],
        Table[Category]
    )
)

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
bugs777
Frequent Visitor

@mahoneypat 

 

Hey, so I used the measure you gave to recreate my static score column as a measure which I then divided by the measure for my participant count column and this has worked! I hope this was the intended use - Thank You!

If its not too much bother can you explain why the measure you provided calculates a row wise average for the score across company and category? I struggle a lot with PBI contexts at the minute 😕

Sure.  The ALLEXCEPT function removes all the filters from the columns of the specified table except the the ones you list.  The table visual provides filters on Category and Company because you added those columns to the visual, and the measure removes filters from any other columns you've included (e.g., Score, if not aggregated).

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Super User IV
Super User IV

Here is a measure expression you can try

 

Avg for Company and Category =
CALCULATE (
    AVERAGE ( Table[Score] ),
    ALLEXCEPT (
        Table,
        Table[Company],
        Table[Category]
    )
)

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors