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

how to count distinct values in a column

Hello all!

 

I'm trying to create a column that shows the total balance of each account. For example, I want the total balance of account 7055 and 8012. How do I go about doing that when I have multiple balances for each account? (There are also 65 seperate accounts with over 39,000 rows, I just cut & pasted a small portion)

 

2016-07-19.png

 

 

1 ACCEPTED SOLUTION

Here is my solution.  Not sure it meets your needs exactly, so let me know if you need more help.  

 

Key formula for % of total:  Percent of Total Balance = Sum(Sheet1[Balance])/Calculate(Sum(Sheet1[Balance]),All(Sheet1))

 

You can use the Acct. dimension i  Pie Chart.  Then, create a new Measure, and use the formula above to create % of Total.  This works because your individual Balance entries sum to the Total Balance.  You don't actually need the Total Balance column, or the Count of Rows for this.

 

If you want the PBIX file and my data source, let me know and I can email it to you, or something.

 

Nate

View solution in original post

7 REPLIES 7
Nhallquist
Helper V
Helper V

Before we provide several possible solutions, we should understand what you are planning to do with this data and how you plan to use it.  Are you wanting to create a Table in the data model that will have a single balance for each account?  Or...  are you just looking to display a single balance for each account back to a user?  Are their any other details you can share with us?

 

Let us know,

 

Nate

@Nhallquist

 

So far I was able to count the rows of each account by creating a new column: Count = 1 and then a new measure: Total = COUNT(FactBalance[Count]. I then created a new table that listed the account code, total (rows), and balance of each account. 

 

However, I need the total balances listed in another column  in order to calculate the percentage and create my desired visualizations.

 

Any advice will be much appreciated! 

Quick Question...  Does the Sum of the Balance column euqal the Total Balance?

The Sum of the balances of all the accounts together does equal the Total Balance. This is what I was able to get so far using COUNT and creating a TOTAL measure:

 

Here is my solution.  Not sure it meets your needs exactly, so let me know if you need more help.  

 

Key formula for % of total:  Percent of Total Balance = Sum(Sheet1[Balance])/Calculate(Sum(Sheet1[Balance]),All(Sheet1))

 

You can use the Acct. dimension i  Pie Chart.  Then, create a new Measure, and use the formula above to create % of Total.  This works because your individual Balance entries sum to the Total Balance.  You don't actually need the Total Balance column, or the Count of Rows for this.

 

If you want the PBIX file and my data source, let me know and I can email it to you, or something.

 

Nate

 Hi Nate,

 

Would you kindly email me your PBIX file and data source? I'll send you my email via private message. 

 

Thanks

 

-Tina

Ultimately I want to calculate the percentage of the total balance of each account (7055, 8012, ect) to the total balances of all accounts ($186,542,728.32). I want to create a visualization such as a bar chart where the percentage of each account can be seen on a trend line. This is a project for work where I am analyzing expense accounts. 

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.