Measure to Count A Value If It Is Within Percentage Range

[jdwalker5]

Hello.  I am trying to recreate the below table in Power BI.  To explain the data, shipping location A delivers to a total of 137 stores.  10 of those stores delivered on time 0-49% of the time, 13 of those stores delivered on time 50-59% of the time, etc.

How do I get the counts for each percentage range?

Shipping Location	0 to 49%	50 to 59%	60 to 69%	70 to 79%	80 to 90%	90 to 94%	95 to 100%	Total Stores
A	10	13	15	31	16	15	37	137
B	11	7	15	22	30	2	3	90

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[v-zhangti · Accepted solution]

Hi, @jdwalker5 

I have simulated the data to answer your question.

Measure:
Count = 
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        [Range] = MAX ( 'Table'[Range] )
            && [Shipping Location] = MAX ( 'Table'[Shipping Location] )
    )
)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdwalker5]

Thank you for the reply.  I ended up doing something similar to the approach you provided and it worked.

[MFelix]

Hi @jdwalker5 

Not sure how you model is setup but you can create a table with the percentages and the maximum and minimum values:

Thelast row is a little bit higher than 100% because has you can see in order to get all the value the maximim is equal to the minimum of the previous category.

Now add the following measure:

Total Stores = 
COUNTROWS (
    CALCULATETABLE (
        GROUPBY( Detaisl, Detaisl[Store], Detaisl[Percentage] ),
        Detaisl[Percentage] >= MIN ( 'Range Limits'[Minimum] )
            && Detaisl[Percentage] < MAX ( 'Range Limits'[Maximum] )
    )
)

See attach PBIX file.

[jdwalker5]

Thanks for the reply.  This would also work for what I needed so I will keep this in my toolbox!