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, 

 

DateEmp_IDEmp_NameEmp_DepartmentAmount_SpentPlace  
9/16/202394Kimberly ChanSales214.57Hinton-Gomez 
9/16/202395Andrew MillerSales77.19Avery-Macdonald 
9/16/202396David JohnsonIT381.46Long-Gonzales 
11/26/202396David JohnsonIT323.46Williams, Ball and Marshall
9/16/202397Jimmy CainOperations483.69King and Sons 
10/9/202397Jimmy CainOperations474.37Jones, Richards and Montoya
10/18/202397Jimmy CainOperations61.49Weaver-Stephens 
11/25/202397Jimmy CainOperations481.56Serrano, Cochran and Tanner
12/11/202397Jimmy CainOperations270.03Hunter, Kirby and Potter
9/16/202398George TravisOperations21.63Andrade, Collier and Davenport
10/6/202398George TravisOperations135.93Stone, Wilkins and Wells
10/29/202398George TravisOperations56.76Miller Ltd  
11/1/202398George TravisOperations181.11Johnson PLC 
12/9/202398George TravisOperations497.66Chavez, Green and Ochoa
12/12/202398George TravisOperations302.66Hernandez-Lindsey 
9/16/202399Daniel HebertMarketing22.83Young, Gonzalez and Bradshaw
10/5/202399Daniel HebertMarketing78.29Morgan-Roth 
11/9/202399Daniel HebertMarketing244.62Montoya-Garrison 
11/25/202399Daniel HebertMarketing375.58Carter, Chen and Zuniga
12/4/202399Daniel HebertMarketing87.21Brown-Navarro 
9/16/2023100Jennifer ThompsonSales459.3Jones Ltd  
10/12/2023100Jennifer ThompsonSales238.13Webb, Mcdaniel and Williamson
11/4/2023100Jennifer ThompsonSales132.01Miller LLC  
11/25/2023100Jennifer ThompsonSales402.75Rodriguez and Sons 
11/28/2023100Jennifer ThompsonSales98.49Thomas-Moore 
9/16/2023101Diana SmithSales86.24Rogers-Winters 
10/15/2023101Diana SmithSales253.28Hogan Group 
11/6/2023101Diana SmithSales300.5Graham, Monroe and Bennett
12/14/2023101Diana SmithSales208.65Tucker, Hogan and Hernandez
12/14/2023101Diana SmithSales319.32Hogan-Hooper 
9/16/2023102Cassidy White DVMFinance397.39Graham Inc 
9/18/2023102Cassidy White DVMFinance140.54Williams-Woods 
10/13/2023102Cassidy White DVMFinance140.57Weaver PLC 
12/4/2023102Cassidy White DVMFinance32.41Oneill and Sons 

 

My requirement is to analyzie people who have used the Credit Card more than one time. 

I created a What-If parameter "Credit Card Used Filter" as a decimal number from 1 to 4 and added it as a slicer. I further used it in my measures so that the slicer works properly when used. 

 

This is how my report looks at the moment, 

 

 

 

However, what I am trying to do is basically create a Bar Graph that will show "Credit Card Used" in the X-Axis (that is, 1, 2, 3 etc.) 

and then show a bar containing the number of employees that fall in those categories. For example, 4000 people used Credit Card only once, 200 used it 2 times, 50 people used it 3 times, 4 people used it 4 times and maybe one more thing that we can add is -> 3 people used it 4+ times.

 

So what I need in my X-Axis is: 1, 2, 3, 4, 4+ 

What I need in my Y-Axis is: Total Employees falling in that category for the given set of filters (Year, Month, Department etc.) 


Thanks to TomMartens, I ended up looking into Dynamic Segmentation and tried to implement the required. I came up with the following DAX but for some reason, it is not giving me any results. And I am failing to understand the reason behind this, 

 

Employee Segments = 

VAR EmployeesInSegment = 
    FILTER(
        ALLSELECTED(Spending_Data), 
        VAR Emp_Count = COUNT(Spending_Data[Emp_ID])
        VAR SegmentForEmp = 
            FILTER(
                'Credit Card Use Values', 
                'Credit Card Use Values'[Value] = Emp_Count
            )
        //VAR IsEmpInSegment = NOT ISEMPTY(SegmentForEmp)
        RETURN SegmentForEmp
    )

Var _Result = 
    CALCULATE(
        COUNTROWS(Spending_Data),
        KEEPFILTERS(EmployeesInSegment)
    )

RETURN _Result

 

Anyone here who can help in achieving this? Thanks. 

If you would like to download the Power BI file containing the sample dataset, please do so from here: https://drive.google.com/file/d/1kyywKZ-KwA_A_oCgxJSHAjxk-idVVJAl/view?usp=sharinghttps://drive.google.com/file/d/1j8rqN9jVjlUiX0o_n2uJ9yBGaGs3AyDj/view?usp=sharing

  • 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 

1 Reply

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion

    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