New Count column in matrix

[hershey]

Hi,

I've the following results in matrix table

I'd like to add a new Count column to indicate total count of customers with spending above or equal 100, results should be as below.

But i'm unable to get the results. Much help is appreciated. Thanks

[hershey_ash]

Hi tamerj1, works perfectly........

one last Q, Activate the column total and rename it "Count" ---> where do i rename this? Currently it's shown as 'Total'

[tamerj1]

@hershey_ash 
Select the visual and follow steps in the screenshot. Please note that I've posted two solutions. The 2nd one sums the counts therefore can be used in a card visual in case needed.

[tamerj1 · Accepted solution]

@hershey 
Please refer to attached sample file

Spending = 
VAR CurrentSpending = [AvgSales]
VAR T1 =
    ADDCOLUMNS ( VALUES ( 'fct_tst'[businessday_YYYY-MM-DD].[Month] ), "@Spending", [AvgSales] )
VAR T2 =
    FILTER ( T1, [@Spending] >= 80 )
VAR CountAbove80 =
    FORMAT ( COUNTROWS ( T2 ), "#" )
RETURN
    IF ( HASONEVALUE ( 'fct_tst'[businessday_YYYY-MM-DD].[Month] ), CurrentSpending, CountAbove80 )

[tamerj1]

Hi @hershey 

you can utilize the total column to disply this value. Activate the column total and rename it "Count". Then replace the measure in the matrix with this measure

Spending = 
VAR CurrentSpending = [AvgSales]
VAR TotalCount =
	SUMX ( 
		VALUES ( FCT_tst[cust] ),
		CALCULATE ( 
			VAR T1 =
				ADDCOLUMNS ( VALUES ( 'fct_tst'[businessday_YYYY-MM-DD].[Month] ), "@Spending", [AvgSales] )
			VAR T2 =
				FILTER ( T1, [@Spending] >= 80 )
			RETURN
				COUNTROWS ( T2 )
		)
	)
RETURN
	IF ( HASONEVALUE ( 'fct_tst'[businessday_YYYY-MM-DD].[Month] ), CurrentSpending, FORMAT ( TotalCount, "#" ) )

[hershey_ash]

Hi tamerj1, 

Thanks a lot for the help.