Forum Discussion

hershey's avatar
hershey
New Member
3 years ago
Solved

New Count column in matrix

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. ...
Need Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
tamerj1
Community Champion
3 years ago

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's avatar
hershey_ash
Regular Visitor
3 years ago

Hi tamerj1, 

 

Thanks a lot for the help.