Help with a measure

[Kazlik]

I wanted to make a measure that shows the occurrence frequency for every EventID per ContentID. For example in the table below I want to see EventID 3798 appears in 2 of 3 ContentID for an occurrence frequency of 66% and so on for each EventID. The EventID can appear one or many times per ContentID.

ContentID EventID
9941912 5027
9941912 52006
9941912 3798
9941912 3798
9941916 3798
9941916 4000
9941916 8809
9941916 415816
9941917 4000
9941917 4000

 

[RickXL]

Hi.

Try a PivotTable. Go to the Insert tab and insert a pivot table.
Drag EventID into the Rows box;
Drag ContentID into the Columns box;
Drag EventID into the ∑ Values box.
Click on the dropdown arrow in the ∑ Values box to change the "Value Field Settings" and change Sum of EventID into Count of EventID.

It looked like this when I tried it:

Excel 2013
	E	F	G	H	I
16	Count of EventID	Column Labels
17	Row Labels	9941912	9941916	9941917	Grand Total
18	3798	2	1		3
19	4000		1	2	3
20	5027	1			1
21	8809		1		1
22	52006	1			1
23	415816		1		1
24	Grand Total	4	4	2	10
Sheet2

 

[theBardd]

This should do it for you.

=COUNT(Table1[ContentID] ) /
CALCULATE( COUNT(Table1[ContentID] ), ALL( Table1[ContentID] )
)

I assume ContentID in rows, EventID in columns.

 

[Kazlik]

Thanks and figured it out with a refresher on The 5-point palm, exploding fxn technique.

SUMX() – The 5-point palm, exploding fxn technique « PowerPivotPro