running total using count?

[whulsbergen]

I'm trying to recreate this risk matrix from excel

To do so, I need to offset coordinates based on how many points there are. In excel I use this:

And then I need to count the coordinates. So for example:

item	x	y	xy	count_xy
item 1	2	3	23	1
item 2	1	3	13	1
item 3	4	4	44	1
item 4	4	4	44	2

In the report I filter by date, so if possible, I want the count to be a dynamic measyre. How do I do this?

I tried running totals, but that doesn't work for items like it does for dates:

count_xy = calculate (count(xy);filter (allselected(xy);count(xy)<= count(???)(xy)))

[harshnathani]

Hi @whulsbergen ,

You will need to create an index column for Item

Then You can use this measure

RT =
VAR CurrentItem =
    SELECTEDVALUE ( 'Table'[Index No] )
RETURN
    CALCULATE (
        COUNT ( 'Table'[XY] ),
        'Table'[Index No] <= CurrentItem,
        ALL ( 'Table'[Index No] )
    )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[whulsbergen]

Sorry @harshnathani , didn't work.

RT =

VAR CurrentItem =

SELECTEDVALUE ( RM_risicodata[XY_index])

RETURN

CALCULATE (

COUNT ( RM_risicodata[XY_index]);

RM_risicodata[XY_index] <= CurrentItem;

ALL( RM_risicodata[XY_index])

)

Result:

As you can see, the RT just shows "1". And with XY = 33, there should have been a 2 at RiskID 97.

[harshnathani]

Hi @whulsbergen ,

Please share some Sample Data in text format .

Also can you share the expected output.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[whulsbergen]

Risk_ID	X	Y	XY	XY_index	expected
98	2	4	24	5	1
102	3	2	32	8	1
95	3	3	33	9	1
97	3	3	33	9	2
99	3	4	34	10	1
100	5	4	54	17	1
103	5	4	54	17	2
96	5	4	54	17	3

Like this @harshnathani ?

The last column is what I expect.

FYI:

I need this to offset the coordinates in the next step so they show up seperately in a bubble diagram in stead of stacked.

RISKID 100: x= 5-0,2=4,8. y=4 + 0,2 = 4,2

RISKID 103: x= 5-0,2=4,8. y=4 - 0,2 = 3,8

RISKID 96: x= 5+0,2=5,2. y=4 + 0,2 = 4,2

[harshnathani]

Hi @whulsbergen ,

You can create a NewColumn

Column = RANKX(FILTER('Table','Table'[XY_index] = EARLIER('Table'[XY_index])),'Table'[XY]+'Table'[Risk_ID],,ASC)

Regards,

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[whulsbergen]

Thanks @harshnathani for your help so far. I like your thinking, but I need to give it some more time. From what I can understand you regard the XY_index like it is a date. But that is not the case, it would be more like counting the unique index-numbers against the total number of that index-number in a column.

And I need it to be flexible, as I have various dates in the table and want to be able to filter without leaving holes in the bubble matrix.

[harshnathani]

Hi @whulsbergen ,

Can you pls share enough sample data and the expected output.

What I could understand from the data which was shared that you needed a running count based on XY_index column.

Regards,

Harsh Nathani