Forum Discussion
Inverse Cumulative Frequency
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.
The results you show (and in your file) are close, but not quite what I want, and they change based on the way the data is sorted (this is not desired). Based on the first 50 rows of data pasted above, here are the results I expect. This seems to be overly difficult in BI as compared to very simple formulas and "fills" in Excel.
| Lic | Frequency | Cumulative Frequency | Inverse Cumulative Frequency | Inverse Cumulative Relative Frequency |
| 1 | 15 | 15 | 196 | 1.0000 |
| 2 | 24 | 39 | 181 | 0.9235 |
| 3 | 5 | 44 | 142 | 0.7245 |
| 4 | 4 | 48 | 98 | 0.5000 |
| 5 | 2 | 50 | 50 | 0.2551 |
I'm looking at your functions trying to figure out exactly what they are doing, but I'm not yet familiar with RANKX and some of the other logic you used.
HotChilli: I thank you for your help as well, but your formula doesn't return the proper values for the Inverse Cumulatave Frequency either. And yes, I now realize adding the Bin column was not necessary.
Thanks!
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.