Forum Discussion
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 | Emp_Name | Emp_Department | Amount_Spent | Place | ||
| 9/16/2023 | 94 | Kimberly Chan | Sales | 214.57 | Hinton-Gomez | ||
| 9/16/2023 | 95 | Andrew Miller | Sales | 77.19 | Avery-Macdonald | ||
| 9/16/2023 | 96 | David Johnson | IT | 381.46 | Long-Gonzales | ||
| 11/26/2023 | 96 | David Johnson | IT | 323.46 | Williams, Ball and Marshall | ||
| 9/16/2023 | 97 | Jimmy Cain | Operations | 483.69 | King and Sons | ||
| 10/9/2023 | 97 | Jimmy Cain | Operations | 474.37 | Jones, Richards and Montoya | ||
| 10/18/2023 | 97 | Jimmy Cain | Operations | 61.49 | Weaver-Stephens | ||
| 11/25/2023 | 97 | Jimmy Cain | Operations | 481.56 | Serrano, Cochran and Tanner | ||
| 12/11/2023 | 97 | Jimmy Cain | Operations | 270.03 | Hunter, Kirby and Potter | ||
| 9/16/2023 | 98 | George Travis | Operations | 21.63 | Andrade, Collier and Davenport | ||
| 10/6/2023 | 98 | George Travis | Operations | 135.93 | Stone, Wilkins and Wells | ||
| 10/29/2023 | 98 | George Travis | Operations | 56.76 | Miller Ltd | ||
| 11/1/2023 | 98 | George Travis | Operations | 181.11 | Johnson PLC | ||
| 12/9/2023 | 98 | George Travis | Operations | 497.66 | Chavez, Green and Ochoa | ||
| 12/12/2023 | 98 | George Travis | Operations | 302.66 | Hernandez-Lindsey | ||
| 9/16/2023 | 99 | Daniel Hebert | Marketing | 22.83 | Young, Gonzalez and Bradshaw | ||
| 10/5/2023 | 99 | Daniel Hebert | Marketing | 78.29 | Morgan-Roth | ||
| 11/9/2023 | 99 | Daniel Hebert | Marketing | 244.62 | Montoya-Garrison | ||
| 11/25/2023 | 99 | Daniel Hebert | Marketing | 375.58 | Carter, Chen and Zuniga | ||
| 12/4/2023 | 99 | Daniel Hebert | Marketing | 87.21 | Brown-Navarro | ||
| 9/16/2023 | 100 | Jennifer Thompson | Sales | 459.3 | Jones Ltd | ||
| 10/12/2023 | 100 | Jennifer Thompson | Sales | 238.13 | Webb, Mcdaniel and Williamson | ||
| 11/4/2023 | 100 | Jennifer Thompson | Sales | 132.01 | Miller LLC | ||
| 11/25/2023 | 100 | Jennifer Thompson | Sales | 402.75 | Rodriguez and Sons | ||
| 11/28/2023 | 100 | Jennifer Thompson | Sales | 98.49 | Thomas-Moore | ||
| 9/16/2023 | 101 | Diana Smith | Sales | 86.24 | Rogers-Winters | ||
| 10/15/2023 | 101 | Diana Smith | Sales | 253.28 | Hogan Group | ||
| 11/6/2023 | 101 | Diana Smith | Sales | 300.5 | Graham, Monroe and Bennett | ||
| 12/14/2023 | 101 | Diana Smith | Sales | 208.65 | Tucker, Hogan and Hernandez | ||
| 12/14/2023 | 101 | Diana Smith | Sales | 319.32 | Hogan-Hooper | ||
| 9/16/2023 | 102 | Cassidy White DVM | Finance | 397.39 | Graham Inc | ||
| 9/18/2023 | 102 | Cassidy White DVM | Finance | 140.54 | Williams-Woods | ||
| 10/13/2023 | 102 | Cassidy White DVM | Finance | 140.57 | Weaver PLC | ||
| 12/4/2023 | 102 | Cassidy White DVM | Finance | 32.41 | Oneill 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:
1 Reply
- PaulDBrown
Community 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: