Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am working on a pivot table that summarizes accounts of clients and client ID's by brackets of available balances,
i.e. accounts with balances less than $3,000 are grouped together, accounts with balances between $3000 and $10,000 are grouped together,etc.
The main Data table I have lists the accounts of all clients, their respective balances, and the respective client ID's (each ID might have several accounts).
I manually created a table which has two columns: "Brackets Column" and "Bracket Code".
I added a calculated column named "Bracket Code" to the Data Table and created a relationship between both tables using the aforementioned calculated column.
Currently I am using pivot table to summarize the number of distinct account numbers and distinct client ID's under each category.
Pivot table will have the following columns:
Column 1 Column2 Column3
"Bracket Category" count of distinct account numbers count of distinct ID's
Column2 is being calculated easily by creating the measure= Count(Distinct(Account Numbers))
However, for client ID's it isn't that simple. When am using the same measure for ID's, the same ID is counted under more than one Bracket Category.
Could anyone help on the syntax of the measure which would count each ID once under its designated "Bracket Category"
Thanks
I still don't get it. Do you want to count accounts only for the highest bracket they achieved?
I want to count ID's under each category bracket based on the total balance of the accounts related to each ID.
In other words, An ID has several accounts. Each account has a specific balance. If for example, the sum of the balances of all accounts related to ID "X" add up to $1,500 then the ID should be counted under category "<3000" and so forth....
Hi again,
thanks for the above solution.
however, I am interested in the distinct id's in each row, and if you notice the total of column count of client id does not match the sum of the above rows.
moreover, each ID should be mentioned only under one category, i.e. the aggregation should be made for the balance of all accounts under each id, then the total should be the indicator used to determine under which category the ID should be counted.
In other words,
The total under column "Count of client ID" is 7 which is correct since there is 7 distinct ID's. However the sum of the rows under the former column is 13 which means some ID's were counted in two or more categories.
My ultimate request is that each ID be counted under one category only based on the total balance of all the accounts under it.
thanks again
In other words,
The total under column "Count of client ID" is 7 which is correct since there is 7 distinct ID's. However the sum of the rows under the former column is 13 which means some ID's were counted in two or more categories.
My ultimate request is that each ID be counted under one category only based on the total balance of all the accounts under it.
thanks again
@zge04 what is the relationship between account numbers and client IDs? is there a hierarchy? which one is the parent? can clients share accounts?
Hello
Under each client ID there could be several account numbers.
Each client ID is distinct.
Client ID is the parent.
Clients with different ID's cannot share same account
Please find below sample data
many thanks in advance
bracket bracket code
<1000 | a |
<3000 | b |
<5000 | c |
<7000 | d |
<10000 | e |
>10000 | f |
accountnbr client id balance bracket code
30 | x | 840 | a |
96 | x | 2600 | b |
89 | x | 100000 | f |
30 | y | 8600 | e |
56 | y | 11000 | f |
82 | z | 450 | a |
19 | w | 79000 | f |
45 | w | 8800 | e |
46 | w | 9000 | e |
40 | w | 910 | a |
88 | v | 3700 | c |
42 | u | 6300 | d |
4 | u | 680 | a |
18 | t | 8400 | e |
98 | t | 7100 | e |
Bracket Bracketcode Count of account nbr countids
<1000 | a | 4 | ?? |
<10000 | e | 5 | ?? |
<3000 | b | 1 | ?? |
<5000 | c | 1 | ?? |
<7000 | d | 1 | ?? |
>10000 | f | 3 | ?? |
The end result is the values in the column fillded with "??"
Thanks
1. link the brackets and facts table via the bracket code
2. Sort the bracket by the bracket code
3. Add Bracket (from the brackets table), client id and accountnbr (from the facts table) to a table visual
4. Set the aggregation for both client id and accountnbr to "Count Distinct"
That's it
In other words,
The total under column "Count of client ID" is 7 which is correct since there is 7 distinct ID's. However the sum of the rows under the former column is 13 which means some ID's were counted in two or more categories.
My ultimate request is that each ID be counted under one category only based on the total balance of all the accounts under it.
thanks again
What should happen when the total balance matches between two categories?
It cant, the total balance lies within one category only.
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |