Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zge04
Frequent Visitor

Use Groupby in a measure

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

11 REPLIES 11
lbendlin
Super User
Super User

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....

zge04
Frequent Visitor

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.

zge04
Frequent Visitor

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
Frequent Visitor

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

lbendlin
Super User
Super User

@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

<1000a
<3000b
<5000c
<7000d
<10000e
>10000f

 

 

accountnbr client id    balance         bracket code

30x840a
96x2600b
89x100000f
30y8600e
56y11000f
82z450a
19w79000f
45w8800e
46w9000e
40w910a
88v3700c
42u6300d
4u680a
18t8400e
98t7100e




Bracket      Bracketcode   Count of account nbr  countids

<1000a4??
<10000e5??
<3000b1??
<5000c1??
<7000d1??
>10000f3??

 

The end result is the values in the column fillded with "??"

 

Thanks

1. link the brackets and facts table via the bracket code

lbendlin_0-1630955814354.png

 

2. Sort the bracket by the bracket code

lbendlin_1-1630955881216.png

3. Add Bracket (from the brackets table), client id and accountnbr (from the facts table) to a table visual

lbendlin_2-1630955978700.png

4. Set the aggregation for both client id and accountnbr to "Count Distinct"

lbendlin_3-1630956039329.png

 

That's it

 

lbendlin_5-1630956212234.png

 

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors