Inverse Cumulative Frequency

[Jack-B-Quick]

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!

[Ashish_Mathur]

Hi,

Could you share the raw data (in a format that can be pasted in an MS Excel file)?

[Jack-B-Quick]

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

[Ashish_Mathur]

Hi,

Is this the result you are expecting?  You may download my PBI file from here.

[Jack-B-Quick]

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!

[Ashish_Mathur]

Hi,

I am not clear about your requirement.  Are you saying that LicCnt should always be in ascending order.

[Cmcmahan · Accepted solution]

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.

[HotChilli]

Inv Cumulative Frequency = CALCULATE(COUNT(SampleLicCnts[LicCnt])
                                    ,FILTER(ALL(SampleLicCnts), SampleLicCnts[BIN] >= MAX(SampleLicCnts[Bin])))

Also, Is there a specific reason for using the Bin formula, rather than just saying BIN = LicCnt?

[HotChilli]

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