I want to count and reflect the number of reason codes against each row

[Anonymous]

Below is the table and count of Reason code is the result I am trying to get in power BI. In first coloumn same Document number is there and against each document either there is reason code present or not present . I want to count and reflect the number of reason codes against each row . 

For eg. 123 document has total of 3 reason codes. so 3 count will reflect against each 123 row.

my excel formula to claculate count of reason code is : 

Docs	Reason Code	Reason Code (Y/N)	Count of Reason code
123	x	1	3
123	0	0	3
123	x	1	3
123	x	1	3
231	0	0	1
231	x	1	1
231	0	0	1
231	0	0	1

I tried below DAX formula but it is giving me the total of all reason codes. But I want sum of Reason code come against each document =

call = CALCULATE(COUNTAX(Sheet1,Sheet1[Order Number]),FILTER(Sheet1,Sheet1[reasoncode1]>0))

[Anonymous]

You could try this as well:

[Count of Reason Codes] =
var __oneDocVisibleAndDirectltyFiltered = HASONEFILTER( T[Docs] )
var __reasonCodeCount =
	CALCULATE(
		SUM ( T[Reason Code (T/N)] ),
		VALUES( T[Docs] ),
		ALL( T )
	)
return
	if(
		__oneDocVisibleAndDirectlyFiltered,
		__reasonCodeCount
	)

Best

Darek

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous 

Try following

=
CALCULATE ( SUM ( Sheet1[reasoncode] ), ALLEXCEPT ( sheet1, sheet1[Docs] ) )

[Anonymous]

This is working. Thanks dude.