Forum Discussion
Inverse Cumulative Frequency
New to BI and just getting my feet wet.
I have the following summary table below based on some backgroud data ('SampleLicCnts' table). The Frequency column was simple of course, just set the value to 'count' vs 'sum'. I created the Bin column so I could use it to calculate the Cummulative Frequecy. Respective formulas for Bin and Cumulative Frequency here...
Bin = FLOOR(DIVIDE('SampleLicCnts'[LicCnt],1),1)Cumulative Frequency = CALCULATE(COUNT(SampleLicCnts[LicCnt])
,FILTER(ALL(SampleLicCnts),SampleLicCnts[BIN_NUMBER]<=MAX(SampleLicCnts[BIN_NUMBER])))
I'm struggling to calculate the Inverse Cumulative Frequency (red text) as show here...
| Licenses | Bin | Frequency | Cumulative Frequency | Inverse Cumulative Frequency |
| 1 | 1 | 2715 | 2715 | 90980 |
| 2 | 2 | 1774 | 4489 | 88265 |
| 3 | 3 | 1083 | 5572 | 83776 |
| 4 | 4 | 565 | 6137 | 78204 |
| 5 | 5 | 249 | 6386 | 72067 |
| 6 | 6 | 100 | 6486 | 65681 |
| 7 | 7 | 44 | 6530 | 59195 |
| 8 | 8 | 27 | 6557 | 52665 |
| 9 | 9 | 16 | 6573 | 46108 |
| 10 | 10 | 8 | 6581 | 39535 |
| 11 | 11 | 6 | 6587 | 32954 |
| 12 | 12 | 1 | 6588 | 26367 |
| 13 | 13 | 3 | 6591 | 19779 |
| 14 | 14 | 2 | 6593 | 13188 |
| 15 | 15 | 2 | 6595 | 6595 |
I tried to reverse to logic of the Cumulative Frequecy by changing the <=MAX to >=MIN but that doesn't produce the correct results. I also referenced this "Inverse Sum" topic but it's not quite the same problem.
Any ideas on how to calculate the Inverse Cumulative Frequency? BTW...Utimately I'm after the Inverse Cumulative Relative Frequency, but to get there I first need the Inverse Cumulative Frequency.
Thanks in advance!
I see what you mean about the data shifting. For this solution, I'm assuming that your Lic value can be sorted in your data and isn't just a random key. If so, you'll likely need to create the Bin column again, just to keep it sortable.
First, I changed the Cumulative Frequency measure to this expression:
Cumulative count = if(HASONEVALUE(Data[LicCnt]),COUNTAX(FILTER(ALL(Data),Data[LicCnt]<=SELECTEDVALUE(Data[LicCnt])),[Count]),BLANK())
Then I set the Inverse Cumulative Frequency measure to this:
Measure1 = SUMX(FILTER(SUMMARIZE(ALL(Data),Data[LicCnt],"Cumulative Count",[Cumulative count]),[LicCnt]>=SELECTEDVALUE(Data[LicCnt])),[Cumulative count])
What this is doing is creating a virtual table where it appends the Cumulative Count to the LicCnt, and then rolls that up to a single table that looks like this:
Then it sums the values from this virtual table where the LicCnt is greater than the current value. This keeps the count when things are re-ordered via sorting, but relies on you being able to sort by LicCnt.
8 Replies
You are on the right track but you don't need to refer to the original table once you have enough data in your summary table.
InvCumulative Frequency = CALCULATE(SUM(Summary[Cumulative Frequency]), FILTER(ALL(Summary), Summary[Bin] < MAX(Summary[Bin])))You could re-write your 'Cumulative Frequency' measure to get the data it needs from your summary table too
Hi,
Could you share the raw data (in a format that can be pasted in an MS Excel file)?
The basis for the data is based off a very simple text (LicCnt.txt) file that looks like the data below (first 50 rows of data copy\pasted). The problem doesn't change, but there are 6595 rows in total with varing values from 1-15 (Frequency of each value presented above). I'd attach the file but I don't see an option to do so.
Also...A slightly ambigious error on my part in the original post. I don't actually have a separate "Summary" table (only the original imported "LicCnt" table). I added the Bin column to the table (per the formula above). I then created a Visual and added LicCnt (change from Sum to Count) to get the Frequency column. Then I created a measure to get the Cumulative Frequency (again per the formula above).
Thanks!
LicCnt
1
1
1
1
1
1
1
1
3
3
3
4
2
2
4
2
1
1
1
1
2
1
1
3
5
2
2
1
2
2
3
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
4
4
5
