Forum Discussion
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.
But i'm unable to get the results. Much help is appreciated. Thanks
hershey
Please refer to attached sample fileSpending = 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 )
5 Replies
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, "#" ) )Hi tamerj1,
Thanks a lot for the help.
hershey
Please refer to attached sample fileSpending = 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 )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'
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.
