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
Icon for Community Champion rankCommunity Champion
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 )