Measure that couns occurences and groups in bins

Question

I am trying to create a measure that counts the occurences of all distinct values in a column and groups them into bins that I defined in a disconnected table. Subsequently a histogram (bar-chart) should be created. A simplified example would look like this:

SalesP
John
Peter
Peter
Robert
John
John

This should lead to the following result:

SalesP	Count
John	3
Peter	2
Robert	1

The bin range is defined in a disconnected table:

From-To	From	To
0-1	0	1
2-3	2	3
4-5	4	5

The final output in Excel should then give me

From-To	Count
0-1	1
2-3	2
4-5	0

I have created a similar measure where I grouped by revenues, but I cant get my head around the problem above.

IF(HASONEVALUE(Container[MinValRange]);COUNTROWS(FILTER(VIB; VIB[AM_VOL]>=VALUES(Container[MinValRange])&&VIB[AM_VOL]<VALUES(Container[MaxValRange])));COUNTROWS(VIB))

Any hints for me?

Answer 1

You need two measures for this

Measure = COUNTROWS(_sales)

Measure2 =
COUNTX (
    ADDCOLUMNS (
        DISTINCT ( ADDCOLUMNS ( _sales, "count", [Measure] ) ),
        "test",
            MAXX (
                FILTER ( _bin, [From] <= EARLIER ( [count] ) && [To] >= EARLIER ( [count] ) ),
                [From-To]
            )
    ),
    [test]
)