Count the number of times a measure breaches certain thresholds

[Anonymous]

Hi,

I have a measure called “rate” which is using Calculate and filter to return a % against each type.

Type	Rate
A	0.50%
B	0.20%
C	1.10%
D	0.00%
E	0.00%
F	0.00%
G	0.00%
H	0.00%
I	1.30%
J	0.00%
K	0.00%

But what I really need is a summary table which returns the number of times the rate falls into each “bin” below, and without showing the Type in a row.

<0.4%	>0.4%	>1%
8	1	2

I have tried a few methods but no luck so far – any help most appreciated!

[v-juanli-msft]

Hi @Anonymous 

It is impossible to group by a measure.

Please change the measure to a caluclated column.

Best Regards
Maggie

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

[Anonymous]

Hi,

The measure "Rate" is a ratio, so it can't go in a calculated column because it cannot be aggregated.

I guess the only way is to create some kind of summarised table where the "Rate" has already been calculated at the required category level.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measures and add measures to table visual, it would be dynamic with the selection from slicers.

rate =
CALCULATE (
    SUM ( 'Table'[d] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Type] = MAX ( 'Table'[Type] ) )
)
    / CALCULATE (
        SUM ( 'Table'[v] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Type] = MAX ( 'Table'[Type] ) )
    )

round_up = ROUNDUP([rate],4)

<0.4% = CALCULATE(DISTINCTCOUNT('Table'[Type]),FILTER('Table',[round_up]<=0.004))

>1% = CALCULATE(DISTINCTCOUNT('Table'[Type]),FILTER('Table',[round_up]>=0.01))

0.4%~1% = CALCULATE(DISTINCTCOUNT('Table'[Type]),FILTER('Table',[round_up]>=0.004&&[round_up]<0.01))

Best Regards
Maggie

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