Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
@Anonymous
For your "Share" column, make sure data type and format are set correctly.
Regards
@vmakhija, share is a Measure, not a column, so the controls are a little different. Perhaps that could be part of the problem?
HI @Anonymous,
I'd like some sample data with expected result to understanding your requirements and coding formula more clearly.
Regards,
Xiaoxin Sheng
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |