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

Count distinct ID's with aggregate balances

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

3 REPLIES 3
MFelix
Super User
Super User

Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



zge04
Frequent Visitor

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

Hi,

Please show the expected result in the column with ??


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.