cancel
Showing results for 
Search instead for 
Did you mean: 
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 III
Super User III

I still don't get it. Do you want to count accounts only for the highest bracket they achieved?

zge04
Frequent Visitor

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 III
Super User III

@zge04 what is the relationship between account numbers and client IDs?  is there a hierarchy?  which one is the parent?  can clients share accounts?

zge04
Frequent Visitor

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

 

 

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

What should happen when the total balance matches between two categories?

zge04
Frequent Visitor

It cant, the total balance lies within one category only.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.