Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
19 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |