Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)))
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!
Sorry @harshnathani , didn't work.
As you can see, the RT just shows "1". And with XY = 33, there should have been a 2 at RiskID 97.
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!
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
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!
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.
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