Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to get a bar chart to show the number of times each maximum value occurs, per distinct ID. The maximum value can be an integer between 1 and 6. An example of data is below:
Episode ID | Most recent form ID |
---|---|
101 | 3 |
101 | 2 |
101 | 1 |
250 | 2 |
250 | 1 |
55 | 2 |
55 | 1 |
638 | 4 |
638 | 3 |
638 | 2 |
638 | 1 |
I am wanting to show a count of the maximum number in the 'most recent form ID' for each distinct episode ID. So, for example, the maximum value of the form ID for episode 101 is 3, so this counts as 1 episode ID. 2 is the maximum number for IDs 55 and 250, so this should count 2 distinct episode IDs. However, my current formula is currently counting every distinct episode ID that has a 2 (even if it is not the max value), so in this example it is returning a count of 4 (not 2).
My current formula is this:
Max(Aggr(Count({<[Opened in past 12 months?] = {'Yes'}>} distinct [Caf Episode ID]), [Most recent Form ID]))
And this produces the following graph:
I know this is wrong, because when I drill down into each column, the single episode ID that falls under '6' also appears in every other 'most recent form ID' group - but I only want it to appear in '6', because this is the maximum 'most recent form ID' for this distinct Episode ID...
Can anyone help? Thank you in advance!
May be try with a calculated dimension
Aggr(Max(ID), [Episode ID])
Expression
Count(DISTINCT [Episode ID])
May be try with a calculated dimension
Aggr(Max(ID), [Episode ID])
Expression
Count(DISTINCT [Episode ID])
Thank you Sunny, that worked! Most appreciated.
hi Sunny,
How would you fix this then?
First, in Straight table to have the max ID per Episode ID:
101 - 3
250 - 2
55 - 2
638 - 4
and then in Bar chart to have the average in general for all Episode ID's?
Avg = 2.75
((3+2+2+4)/4)
Thank you in advance
Hi,
Can you please help me with proper expression.
Thanks,
Sagar