Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Keith011
Helper III
Helper III

creating a measure that does simple division but also takes filter into account

Hi there,

 

this table is my raw data

respondentIDAttributeValue
1what is your favourite colorred
2what is your favourite colorred
3what is your favourite colorred
4what is your favourite colorblue
5what is your favourite colorgreen
6what is your favourite colorblack
7what is your favourite colorblue
8what is your favourite colorblue
9what is your favourite coloryellow
10who is your favourite superherospiderman
11who is your favourite superherospiderman
12who is your favourite superherosuperman
13who is your favourite superherobatman
14who is your favourite superherobatman
15who is your favourite superherobatman
16who is your favourite superherobatman

 

I would like to create a measure that does a simple COUNT of Value divided by DistinctCount of respondent ID.

 

For example,

red would be 3 / 16

blue would be 3 / 16

yellow, black, green would be 1 / 16

spiderman would be 2 / 16

superman would be 1 / 16

batman would be 4 / 16

 

3 REPLIES 3
Keith011
Helper III
Helper III

why wouldn't this work?

Keith011_0-1671329010004.png

 

Mahesh0016
Super User
Super User

simple COUNT of value divided by DistinctCount =
VAR countValue = CALCULATE(COUNTROWS(TableName),ALL(TableName))
VAR discount = CALCULATE(COUNT(TableName[Value]),ALLEXCEPT(TableName[Value]))


RETURN
DIVIDE(discount,countValue,0)
 
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! 

Is this done in measure? if Yes, the formula is working but the outcome im getting are 0. It's not doing the right calculations @Mahesh0016 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.