How to calculate Cumulative Count based on Bin

[PBI_newuser]

Hi, how to create the cumulative job count by using Bin? Here is the sample data.

Table:

Job	Hours	Bin
J-1	12	0-12
J-2	10	0-12
J-3	39	37-48
J-4	18	13-24
J-5	25	25-36
J-6	87	85-96
J-7	40	37-48
J-8	42	37-48
J-9	38	37-48
J-10	34	25-36
J-11	19	13-24
J-12	65	61-72
J-13	66	61-72
J-14	79	73-84
J-15	55	49-60

Expected Output:

Bin	Job Count	Cumulative Count
0-12	2	2
13-24	2	4
25-36	2	6
37-48	4	10
49-60	1	11
61-72	2	13
73-84	1	14
85-96	1	15

[MohammadLoran25]

Hi @PBI_newuser ,

Assume your table name is JobTable.

Follow steps below:

1-Create a Calculated Column:

BinID = CONVERT(SUBSTITUTE(JobTable[Bin],"-","") ,INTEGER)

2-Create a Calculated Column:

BinOrderIndex = RANKX(JobTable,JobTable[BinID],JobTable[BinID],ASC, DENSE)

3-Create a measure:

JobCount = COUNT(JobTable[Job])

Then your Cumulative measure would be like:

 CumulativeJobCount =
CALCULATE (
    [JobCount],
    FILTER (
        ALL ( JobTable ),
        JobTable[BinOrderIndex] <= MAX ( JobTable[BinOrderIndex] )
    )
)

If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.

Regards,

Loran

[PBI_newuser]

Hi @MohammadLoran25 , I tried to use the suggested column/measure but it doesn't work when I select product. Here is sample data.

[MohammadLoran25]

You have not provided any info about your product table and its relationship with your JobTable.

Please Provide more details.

[PBI_newuser]

Hi @MohammadLoran25 , I want the cumulative count calculate based on the product selected. For example, when I select Product D, the job count for "0-24" bin is 4 and the cumulative count need to be 4 also. 

[MohammadLoran25]

I understood.

But what is your data model? What is the relationship between Product table and sample data you provided?

[PBI_newuser]

Can you access to the pbix file here to see the data model?

https://drive.google.com/file/d/1xYKKBobGldw1SmNq0tC9ddkg4YkzxBeX/view?usp=sharing

[MohammadLoran25 · Accepted solution]

Try This:

CumulativeJobCount =
CALCULATE (
    [JobCount],
    FILTER (
        ALLSELECTED ( JobTable ),
        JobTable[BinOrderIndex] <= MAX ( JobTable[BinOrderIndex] )
    )
)

[PBI_newuser]

@MohammadLoran25  It works!!! Thank you sooooooooo much 🙂

[MohammadLoran25]

You're welcome.