Calculating the field with the highest share in a record

[micjohn7]

Hello, 

I'm working with a large dataset of US government grants and contracts. Each contract is classified in multiple ways - one of which is a field called "international_purpose_name". I am trying to determine the main purpose of each contract by determining which purpose has the highest share of funding within each contract. 

I've pasted sample data below. The activity_project_number is the identifer of each contract. This data is for only one project number but there are thousands in the dataset. Each international purpose may show up more than once in combination with the project number due to other fields in the dataset. An example is the US category name field below. 

Amount Per Activity is a custom measure I created while Amount by purpose is a custom column I created. The code for Amount by Purpose is included below my data table. I'm struggling with how to get beyond this. I need to do two further things:

1. Identify which international purpose has the highest share of funding for each activity_project_number and return that value into a new column where the result shows up in every row associated with the activity_project_number. So, in the data below, I need "Population policy and administrative management" to show up in a new column on every row of this project. I would name that column "Primary_Purpose"
2. I also need to determine the % share that each purpose has of the project and then perform a separate analysis to identify project numbers that have over a certain percentage (e.g. 60%) of a particular international_purpose_name. I'm not sure what percentage I'll use yet, but I need to analyze which projects have certain substantial components of a purpose. 

Thanks for your support. I've been getting stuck on creating a percentage that breaks down appropriately and then determining how to select the highest percentage and get that into a new column. I am still fairly new to Power BI and DAX. 

activity_project_number	Sum of current_dollar_amount	international_purpose_name	us_category_name	Amount Per Activity	Amount by Purpose
7200AA18LA00008	$100,000	Agricultural policy and administrative management	Economic Development	$97,878,893	$240,000
7200AA18LA00008	$140,000	Agricultural policy and administrative management	Program Support	$97,878,893	$240,000
7200AA18LA00008	$900,000	Basic drinking water supply and basic sanitation	Health	$97,878,893	$900,000
7200AA18LA00008	$228,296	Basic health care	Health	$97,878,893	$228,296
7200AA18LA00008	$3,379,189	Basic nutrition	Health	$97,878,893	$3,379,189
7200AA18LA00008	$1,339,150	Democratic participation and civil society	Democracy, Human Rights, and Governance	$97,878,893	$1,339,150
7200AA18LA00008	$15,724,832	Family planning	Health	$97,878,893	$15,724,832
7200AA18LA00008	$100,000	Food Assistance	Humanitarian Assistance	$97,878,893	$100,000
7200AA18LA00008	$7,774,296	Health policy and administrative management	Health	$97,878,893	$7,774,296
7200AA18LA00008	$12,999,554	Human rights	Democracy, Human Rights, and Governance	$97,878,893	$12,999,554
7200AA18LA00008	$371,852	Infectious disease control	Health	$97,878,893	$371,852
7200AA18LA00008	$1,664,075	Malaria control	Health	$97,878,893	$1,664,075
7200AA18LA00008	$1,550,000	Medical research	Health	$97,878,893	$1,550,000
7200AA18LA00008	$3,189,640	Population policy and administrative management	Health	$97,878,893	$19,940,267
7200AA18LA00008	$16,750,627	Population policy and administrative management	Program Support	$97,878,893	$19,940,267
7200AA18LA00008	$13,885,921	Reproductive health care	Health	$97,878,893	$13,885,921
7200AA18LA00008	$11,654,679	Social Protection	Education and Social Services	$97,878,893	$11,654,679
7200AA18LA00008	$3,603,071	STD control including HIV/AIDS	Health	$97,878,893	$3,603,071
7200AA18LA00008	$2,523,711	Tuberculosis control	Health	$97,878,893	$2,523,711
Total	$97,878,893

Here is the DAX for the custom column

Amount by Purpose = 
CALCULATE(
    SUM(Foreign_Assistance[current_dollar_amount]),
    ALLEXCEPT(Foreign_Assistance, Foreign_Assistance[activity_project_number], Foreign_Assistance[international_purpose_name])
)

[micjohn7 · Accepted solution]

Update: I believe I've solved part of my challenge. I've used this code to determine the primary purpose for each project number. I would love to hear if you like this solution or see a better way of achieving it. 

I'm still working out how to get the percentage to calculate correctly. 

Primary_Purpose = 
VAR MaxPurposeAmount = 
    CALCULATE(
        MAX(Foreign_Assistance[Amount_Per_Purpose]),
        ALLEXCEPT(Foreign_Assistance, Foreign_Assistance[activity_project_number])
    )
VAR SelectedPurpose = 
    CALCULATE(
        MAX(Foreign_Assistance[international_purpose_name]),
        FILTER(
            Foreign_Assistance,
            Foreign_Assistance[Amount_Per_Purpose] = MaxPurposeAmount
        )
    )
RETURN SelectedPurpose

[micjohn7 · Accepted solution]

Update: I believe I've solved part of my challenge. I've used this code to determine the primary purpose for each project number. I would love to hear if you like this solution or see a better way of achieving it. 

I'm still working out how to get the percentage to calculate correctly. 

Primary_Purpose = 
VAR MaxPurposeAmount = 
    CALCULATE(
        MAX(Foreign_Assistance[Amount_Per_Purpose]),
        ALLEXCEPT(Foreign_Assistance, Foreign_Assistance[activity_project_number])
    )
VAR SelectedPurpose = 
    CALCULATE(
        MAX(Foreign_Assistance[international_purpose_name]),
        FILTER(
            Foreign_Assistance,
            Foreign_Assistance[Amount_Per_Purpose] = MaxPurposeAmount
        )
    )
RETURN SelectedPurpose

[micjohn7 · Accepted solution]

Update #2: I believe I have this solved now. But I don't know why 😂

Yesterday, the percentages were showing as in my sample data. Today, I am getting the correct %s. 

I am using this to determine the percent share:

Percentage_Share = 
DIVIDE(
    [Amount_Per_Purpose],
    [Amount_Per_Activity],
    0
)

[micjohn7]

Here is the expected outcome below. The Intl Purpose Share calculates the percentage that each purpose is of the whole. The Primary purpose assigns the purpose with the highest share to all rows of data for that project number. 

This is the main thing I am trying to achieve. 

There is another piece to my analysis as well which is to analyze the Intl Purpose Share to identify project numbers that have over a certain percentage, but I won't do that in the table. I think I can achieve that through filters after the table is setup, but if you have any advice there, I would certainly take it!

activity_project_number	Sum of current_dollar_amount	international_purpose_name	us_category_name	Amount Per Activity	Amount by Purpose	Intl Purpose Share	Primary Purpose
7200AA18LA00008	$100,000	Agricultural policy and administrative management	Economic Development	$97,878,893	$240,000	0%	Population policy and administrative management
7200AA18LA00008	$140,000	Agricultural policy and administrative management	Program Support	$97,878,893	$240,000	0%	Population policy and administrative management
7200AA18LA00008	$900,000	Basic drinking water supply and basic sanitation	Health	$97,878,893	$900,000	1%	Population policy and administrative management
7200AA18LA00008	$228,296	Basic health care	Health	$97,878,893	$228,296	0%	Population policy and administrative management
7200AA18LA00008	$3,379,189	Basic nutrition	Health	$97,878,893	$3,379,189	3%	Population policy and administrative management
7200AA18LA00008	$1,339,150	Democratic participation and civil society	Democracy, Human Rights, and Governance	$97,878,893	$1,339,150	1%	Population policy and administrative management
7200AA18LA00008	$15,724,832	Family planning	Health	$97,878,893	$15,724,832	16%	Population policy and administrative management
7200AA18LA00008	$100,000	Food Assistance	Humanitarian Assistance	$97,878,893	$100,000	0%	Population policy and administrative management
7200AA18LA00008	$7,774,296	Health policy and administrative management	Health	$97,878,893	$7,774,296	8%	Population policy and administrative management
7200AA18LA00008	$12,999,554	Human rights	Democracy, Human Rights, and Governance	$97,878,893	$12,999,554	13%	Population policy and administrative management
7200AA18LA00008	$371,852	Infectious disease control	Health	$97,878,893	$371,852	0%	Population policy and administrative management
7200AA18LA00008	$1,664,075	Malaria control	Health	$97,878,893	$1,664,075	2%	Population policy and administrative management
7200AA18LA00008	$1,550,000	Medical research	Health	$97,878,893	$1,550,000	2%	Population policy and administrative management
7200AA18LA00008	$3,189,640	Population policy and administrative management	Health	$97,878,893	$19,940,267	20%	Population policy and administrative management
7200AA18LA00008	$16,750,627	Population policy and administrative management	Program Support	$97,878,893	$19,940,267	20%	Population policy and administrative management
7200AA18LA00008	$13,885,921	Reproductive health care	Health	$97,878,893	$13,885,921	14%	Population policy and administrative management
7200AA18LA00008	$11,654,679	Social Protection	Education and Social Services	$97,878,893	$11,654,679	12%	Population policy and administrative management
7200AA18LA00008	$3,603,071	STD control including HIV/AIDS	Health	$97,878,893	$3,603,071	4%	Population policy and administrative management
7200AA18LA00008	$2,523,711	Tuberculosis control	Health	$97,878,893	$2,523,711	3%	Population policy and administrative management

[lbendlin]

Please show the expected outcome based on the sample data you provided.