Reply
micjohn7
Frequent Visitor

Calculating the field with the highest share in a record

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_numberSum of current_dollar_amountinternational_purpose_nameus_category_nameAmount Per ActivityAmount by Purpose
7200AA18LA00008$100,000Agricultural policy and administrative managementEconomic Development$97,878,893$240,000
7200AA18LA00008$140,000Agricultural policy and administrative managementProgram Support$97,878,893$240,000
7200AA18LA00008$900,000Basic drinking water supply and basic sanitationHealth$97,878,893$900,000
7200AA18LA00008$228,296Basic health careHealth$97,878,893$228,296
7200AA18LA00008$3,379,189Basic nutritionHealth$97,878,893$3,379,189
7200AA18LA00008$1,339,150Democratic participation and civil societyDemocracy, Human Rights, and Governance$97,878,893$1,339,150
7200AA18LA00008$15,724,832Family planningHealth$97,878,893$15,724,832
7200AA18LA00008$100,000Food AssistanceHumanitarian Assistance$97,878,893$100,000
7200AA18LA00008$7,774,296Health policy and administrative managementHealth$97,878,893$7,774,296
7200AA18LA00008$12,999,554Human rightsDemocracy, Human Rights, and Governance$97,878,893$12,999,554
7200AA18LA00008$371,852Infectious disease controlHealth$97,878,893$371,852
7200AA18LA00008$1,664,075Malaria controlHealth$97,878,893$1,664,075
7200AA18LA00008$1,550,000Medical researchHealth$97,878,893$1,550,000
7200AA18LA00008$3,189,640Population policy and administrative managementHealth$97,878,893$19,940,267
7200AA18LA00008$16,750,627Population policy and administrative managementProgram Support$97,878,893$19,940,267
7200AA18LA00008$13,885,921Reproductive health careHealth$97,878,893$13,885,921
7200AA18LA00008$11,654,679Social ProtectionEducation and Social Services$97,878,893$11,654,679
7200AA18LA00008$3,603,071STD control including HIV/AIDSHealth$97,878,893$3,603,071
7200AA18LA00008$2,523,711Tuberculosis controlHealth$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])
)

 

 

 

2 ACCEPTED SOLUTIONS
micjohn7
Frequent Visitor

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

 

View solution in original post

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
)

 

View solution in original post

4 REPLIES 4
micjohn7
Frequent Visitor

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

 

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

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_numberSum of current_dollar_amountinternational_purpose_nameus_category_nameAmount Per ActivityAmount by PurposeIntl Purpose SharePrimary Purpose
7200AA18LA00008$100,000Agricultural policy and administrative managementEconomic Development$97,878,893$240,0000%Population policy and administrative management
7200AA18LA00008$140,000Agricultural policy and administrative managementProgram Support$97,878,893$240,0000%Population policy and administrative management
7200AA18LA00008$900,000Basic drinking water supply and basic sanitationHealth$97,878,893$900,0001%Population policy and administrative management
7200AA18LA00008$228,296Basic health careHealth$97,878,893$228,2960%Population policy and administrative management
7200AA18LA00008$3,379,189Basic nutritionHealth$97,878,893$3,379,1893%Population policy and administrative management
7200AA18LA00008$1,339,150Democratic participation and civil societyDemocracy, Human Rights, and Governance$97,878,893$1,339,1501%Population policy and administrative management
7200AA18LA00008$15,724,832Family planningHealth$97,878,893$15,724,83216%Population policy and administrative management
7200AA18LA00008$100,000Food AssistanceHumanitarian Assistance$97,878,893$100,0000%Population policy and administrative management
7200AA18LA00008$7,774,296Health policy and administrative managementHealth$97,878,893$7,774,2968%Population policy and administrative management
7200AA18LA00008$12,999,554Human rightsDemocracy, Human Rights, and Governance$97,878,893$12,999,55413%Population policy and administrative management
7200AA18LA00008$371,852Infectious disease controlHealth$97,878,893$371,8520%Population policy and administrative management
7200AA18LA00008$1,664,075Malaria controlHealth$97,878,893$1,664,0752%Population policy and administrative management
7200AA18LA00008$1,550,000Medical researchHealth$97,878,893$1,550,0002%Population policy and administrative management
7200AA18LA00008$3,189,640Population policy and administrative managementHealth$97,878,893$19,940,26720%Population policy and administrative management
7200AA18LA00008$16,750,627Population policy and administrative managementProgram Support$97,878,893$19,940,26720%Population policy and administrative management
7200AA18LA00008$13,885,921Reproductive health careHealth$97,878,893$13,885,92114%Population policy and administrative management
7200AA18LA00008$11,654,679Social ProtectionEducation and Social Services$97,878,893$11,654,67912%Population policy and administrative management
7200AA18LA00008$3,603,071STD control including HIV/AIDSHealth$97,878,893$3,603,0714%Population policy and administrative management
7200AA18LA00008$2,523,711Tuberculosis controlHealth$97,878,893$2,523,7113%Population policy and administrative management
lbendlin
Super User
Super User

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)