cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nathan_W Frequent Visitor
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
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
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: | |
vmakhija Member
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.

Percent.PNG

 

Regards

Highlighted
Nathan_W Frequent Visitor
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?

Screenshot_4.png

 

 

 

Nathan_W Frequent Visitor
Frequent Visitor

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

Sure, here you go:

 

ItemShare
Item 163.10%
Item 263.12%
Item 364.27%
Item 464.37%
Item 566.23%
Item 666.39%
Item 766.74%
Item 869.16%
Item 974.56%
Item 1076.48%
Item 1177.06%
Item 1282.85%
Item 1386.31%
Item 1491.07%
Item 1592.97%
Item 1694.72%
Item 1796.91%
Item 1898.08%
Item 1999.32%
Item 20100.00%
Item 21100.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
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: | |