Help with Expenses by County Solution

[jdixon]

I am trying to get my expenses broke out by County based on my Service Categories and I am having trouble. I will try to be detailed as possible, it is a bit complicated (or maybe I am just making it that way).

First my Service Units are in a table called Combined, and my expenses are in a table called MIP Actual Ledger. They are connected by a calendar index table.

My combined table is also connected to a County Index table, as some services outside of our service area need to be listed as Other.

I have tried several different ways to get what I need with no success. My units (Units Served = SUM(Combined[Units])) are split into categories ‘Combined’[Service], and also by ‘County Index’[County Name]. My Expenses (Expenses = SUMX(Filter('MIP GL Code','MIP GL Code'[GL Type] = "EXP"),'MIP Actual Ledger'[Total Actual Amount])) are grouped by GL or Program Code into different services by conditional column(see end for conditions).

Ideally, I would like to take the total units by service and divide then by the total units by service to get a unit rate. Then I would like to take that unit rate, by service and multiply it by the total units by county by service. I need these to be able to automatically update when I change data ranges and such. I am not even sure it is possible.

Column Conditions:

= Table.AddColumn(#"Renamed Columns", "Service", each if [Program Code] = "234" then "Guardianship" else if [Program Code] = "231" then "Case Management" else if [Program Code] = "230" then "Case Management" else if [Program Code] = "235" then "Case Management" else if [Program Code] = "232" then "Case Management" else if [Program Code] = "233" then "Case Management" else if [GL Code] = "57092" then "Adaptive Aid" else if [GL Code] = "57331" then "Adaptive Aid" else if [GL Code] = "57139" then "Adaptive Aid" else if [GL Code] = "57330" then "Adaptive Aid" else if [GL Code] = "57130" then "Adaptive Aid" else if [Program Code] = "245" then "Caregiver/Respite" else if [Program Code] = "217" then "Caregiver/Respite" else if [Program Code] = "256" then "Health & Wellness" else if [GL Code] = "57310" then "Home Delivered Meals" else if [GL Code] = "57110" then "Home Delivered Meals" else if [Program Code] = "241" then "Information & Assistance" else if [GL Code] = "57250" then "In-Home Services" else if [GL Code] = "57050" then "In-Home Services" else if [GL Code] = "57260" then "In-Home Services" else if [GL Code] = "57060" then "In-Home Services" else if [GL Code] = "57010" then "In-Home Services" else if [GL Code] = "57280" then "In-Home Services" else if [GL Code] = "57080" then "In-Home Services" else if [GL Code] = "57269" then "In-Home Services" else if [GL Code] = "57071" then "In-Home Services" else if [GL Code] = "57272" then "In-Home Services" else if [GL Code] = "57072" then "In-Home Services" else if [GL Code] = "57320" then "In-Home Services" else if [Program Code] = "214" then "Legal Assistance" else if [Program Code] = "215" then "Ombudsman" else if [Program Code] = "255" then "Other Services" else if [Program Code] = "259" then "Other Services" else if [Program Code] = "258" then "Other Services" else if [Program Code] = "221" then "Transportation" else if [Program Code] = "500" then "Transportation" else if [Program Code] = "271" then "Congregate" else if [GL Code] = "57011" then "In-Home Services" else [Program Code])

[Mahesh0016]

@jdixon Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[jdixon]

@Mahesh0016 - here is the sample report - https://app.powerbi.com/groups/me/reports/c10495c2-f926-4f38-910a-e033e39bb137/ReportSection204c74d4... 

Here is what I am going for: 

[jdixon]

I will see what I can do, but it might take me a couple of days to come up with something. As I am sure you can relate, we have a lot of things going on at the moment. Thanks!