Search code examples
powerbihistogramdaxpowerquerypowerpivot

Measure that couns occurences and groups in bins

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?

Solution

  • 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]
)

    Solution