topic Calculate the count of column equaling measure in DAX Commands and Tips

Calculate the count of column equaling measure

hansei — Tue, 21 May 2019 01:38:38 GMT

I have a table with a calculated column

Allowed = [Billed] - [Adjusted]

ID  Code  Billed  Adjusted  Allowed
1   A     100     50        50      
2   A     200     150       50      
3   A     100     25        75      
4   A     200     150       50      
5   A     100     25        75      
6   B     100     20        80       
7   B     125     45        80       
8   B     200     120       80
9   B     100     25        75      
10  B     145     70        75

I also have a measure for the arithmetic mode of [Allowed]

Allowed Mode := MINX(
	TOPN(
		1, 
		ADDCOLUMNS(
			VALUES('Charge Fact'[Allowed]), 
			"Frequency", CALCULATE(COUNT('Charge Fact'[Allowed]))
		), 
		[Frequency], 
		0
	), 
	'Charge Fact'[Allowed]
)

So, with the example data a matrix might appear like

Code   Count   Avg   Mode   Freq
A      5       60    50     3
B      5       78    80     3
Total  10      69    75     4

What I would like to do is get the measure for how many rows are equal to the mode. [Freq] in the above matrix, but haven't been able to

Re: Calculate the count of column equaling measure

parry2k — Tue, 21 May 2019 03:50:35 GMT

hansei not fully sure if this is what you are looking for, here is the measaure to calculate frequency

Allowed Mode Count = 
VAR __mode = [Allowed Mode]
RETURN
 CALCULATE( COUNT( Table2[ID] ),
   
    Table2[Allowed] =__mode
)

Re: Calculate the count of column equaling measure

hansei — Tue, 21 May 2019 06:35:45 GMT

Exactly what I was looking for. Thanks parry2k