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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

HI @Anonymous,

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
vmakhija
Post Prodigy
Post Prodigy

@Anonymous

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

Percent.PNG

 

Regards

Anonymous
Not applicable

@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

 

 

 

v-shex-msft
Community Support
Community Support

HI @Anonymous,

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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?

HI @Anonymous,

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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