- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- 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"
- 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])
)
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please show the expected outcome based on the sample data you provided.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
08-27-2024 11:39 AM | |||
08-21-2024 11:14 AM | |||
05-15-2024 06:43 PM | |||
08-29-2024 12:51 PM | |||
08-05-2024 08:54 AM |
User | Count |
---|---|
113 | |
80 | |
55 | |
54 | |
44 |
User | Count |
---|---|
168 | |
114 | |
74 | |
61 | |
52 |