Custom Column to count rows and divide

[dwalters]

Situation: I have a list of phone numbers associated to multiple subaccounts (sub account 1 has 10 lines, sub  account 2 may have 27 lines, etc.). Each sub account has a flat fee associated (sub account 1 = $350, sub account 2 =$273, etc.) that needs to be divided equally across all lines within that account. I have merged the original tables into one that has a a column for phone number, sub account number, and account monthly charges. For what it's worth, I am appending the file each month with invoice info, so there will be duplicate phone numbers and sub-account rows but I don't think that would matter as the flat fee is static regardless of billing month.

How do I create a custom column that results in the math working out? 

   

[Shaurya]

Hi @dwalters,

Form what I understand, you want to assign monthly charge values in a new column based on the subaccount number. You can use this DAX formula to easily do that.

Monthly Charge = SWITCH(TRUE(),'Table'[Subaccount]=1,350,'Table'[Subaccount]=2,273,'Table'[Subaccount]=3,250,0)

Did I answer your question? Mark this post as a solution if I did!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

[Barthel · Accepted solution]

Hey @dwalters,
You can perform the distribution based on the 'Group by' function. Go to your merged table and right click on the subsaccount column. Then select 'Group by'.

Group the table by 'subaccount' and 'charge'. Add an extra column with 'All Rows' and a column with either 'Count Rows' or 'Count Distinct Rows'. If you want to take into account duplicate numbers in your distribution, choose 'Count Rows'. If not, choose 'Count Distinct Rows'.

Expand the 'All Rows' column with the 'number' column. Now you have your original table again, with the number of wireless numbers per sub-account.  The last step is to add a custom column, where the charge is divided by the number of wireless numbers.

[dwalters]

Thanks, that is exactly what I needed. I've not had a lot of exposure nor success with grouping (particularly in the power query area of pbi). I really appreciate the help!