Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Problem in doing Dynamic Segmentation of the data

Hi, I have the data of Employees and their Credit Card spends. The data contains Employees having multiple spends in the same month.  Sample of the dataset is provided below,    Date Emp_ID E...
  • PaulDBrown's avatar
    2 years ago

    See if this works for you...

    To calculate the number of transactions by employee:

     

    Number of transactions by User ID =
    VAR _ID =
        MAX ( Spending_Data[Emp_ID] )
    RETURN
        CALCULATE (
            COUNT ( Spending_Data[Date] ),
            FILTER ( Spending_Data, Spending_Data[Emp_ID] = _ID )
        )

     

    To calculate the number of employees by number of transactions

     

    Employees by number of transactions =
    VAR _Table =
        COUNTROWS (
            FILTER (
                VALUES ( Spending_Data[Emp_ID] ),
                [Number of transactions by User ID]
                    = SELECTEDVALUE ( 'Credit Card Used Filter'[Credit Card Used Filter] )
            )
        )
    VAR _4Plus =
        COUNTROWS (
            FILTER (
                VALUES ( Spending_Data[Emp_ID] ),
                [Number of transactions by User ID] >= 4
            )
        )
    RETURN
        IF (
            SELECTEDVALUE ( 'Credit Card Used Filter'[Credit Card Used Filter] ) > 3,
            _4Plus,
            _Table
        )

     

     

    To get

    Link to file:

    https://1drv.ms/u/s!AhGKDZz6e4HehKh1p8KY73_fpvAapA?e=ucajGn