cancel
Showing results for
Did you mean:
Frequent Visitor

## Distinct Count of a column based on calculation of another column

I have a table of items, and each has a percentage of share. I would like to return the number of distint items where that percentage is below 70%. This is what I've been using:

CountShareUnder70 = CALCULATE(DISTINCTCOUNT('Table'[Items]),FILTER('Table',[Share]<.7))

The idea is that if this is the data:

Item1 60%

Item2 65%

Item3 80%

Item4 50%

The measure would return a 1 on all items except item 3, and a total of 3. But what I'm seeing is that it returns a 1 on every row, and a total of 4. What am I missing?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Distinct Count of a column based on calculation of another column

HI @Nathan_W,

Maybe you can try to use following measure if it works for you scenario.

```CountShareUnder70 =
CALCULATE (
DISTINCTCOUNT ( 'Table2'[Item] ),
FILTER ( ALLSELECTED ( 'Table2' ), [Share] < .7 ),
VALUES ( Table2[Item] )
)
```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
5 REPLIES 5
Community Support Team

## Re: Distinct Count of a column based on calculation of another column

HI @Nathan_W,

I'd like some sample data with expected result to understanding your requirements and coding formula more clearly.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Member

## Re: Distinct Count of a column based on calculation of another column

@Nathan_W

For your "Share" column, make sure data type and format are set correctly.

Regards

Highlighted
Frequent Visitor

## Re: Distinct Count of a column based on calculation of another column

@vmakhija, share is a Measure, not a column, so the controls are a little different. Perhaps that could be part of the problem?

Frequent Visitor

## Re: Distinct Count of a column based on calculation of another column

Sure, here you go:

 Item Share Item 1 63.10% Item 2 63.12% Item 3 64.27% Item 4 64.37% Item 5 66.23% Item 6 66.39% Item 7 66.74% Item 8 69.16% Item 9 74.56% Item 10 76.48% Item 11 77.06% Item 12 82.85% Item 13 86.31% Item 14 91.07% Item 15 92.97% Item 16 94.72% Item 17 96.91% Item 18 98.08% Item 19 99.32% Item 20 100.00% Item 21 100.00%

We have 21 items. The goal would be to create a Measure that could tell me that there are 8 items under 70%, so I could create a Card based off that Measure, returning that number to the user. Keep in mind that these are not 21 table rows, these are 21 items with many table rows aggregated, so we can't just filter the underlying table to get an easy answer, we need a Measure that works off the existing Share measure to tell us that, for the time period filtered here, X number of items are under 70%.

Does that help?

Community Support Team

## Re: Distinct Count of a column based on calculation of another column

HI @Nathan_W,

Maybe you can try to use following measure if it works for you scenario.

```CountShareUnder70 =
CALCULATE (
DISTINCTCOUNT ( 'Table2'[Item] ),
FILTER ( ALLSELECTED ( 'Table2' ), [Share] < .7 ),
VALUES ( Table2[Item] )
)
```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |