Forum Discussion
how to count distinct values in a column
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
Helper VBefore 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
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.
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!
- Nhallquist
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: