Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Please help. I have a table that looks like this and woud like to calculate the following:
1. DAX formula to calculate $ for AA as % of total $ for each project.
2. DAX formula to calculate $ for AA as % of $ for BB for each project
3. DAX formula to calculate $ for AA as % of $s for AA+BB for each prpject
Project | WBS Level 2 | WBS Level 3 | $ |
X/9999 | AA | AA_001 | 100 |
X/9999 | AA | AA_002 | 70 |
X/9999 | AA | AA_003 | 70 |
X/9999 | AA | AA_004 | 70 |
X/9999 | AA | AA_005 | 69 |
X/9999 | BB | BB_001 | 50 |
X/9999 | BB | BB_002 | 69 |
X/9999 | BB | BB_003 | 70 |
X/9999 | BB | BB_004 | 20 |
X/9999 | CC | CC_001 | 0 |
X/9999 | CC | CC_002 | 0 |
X/9999 | CC | CC_003 | 0 |
X/9999 | DD | DD_001 | -50 |
X/10000 | AA | AA_001 | 90 |
X/10000 | AA | AA_002 | 90 |
X/10000 | AA | AA_003 | 90 |
X/10000 | BB | BB_001 | 100 |
X/10000 | BB | BB_002 | 30 |
X/10000 | BB | BB_003 | 80 |
X/10000 | BB | BB_004 | 90 |
X/10000 | BB | BB_005 | 70 |
X/10000 | CC | CC_001 | 80 |
X/10000 | DD | DD_001 | 100 |
X/10000 | EE | EE_001 | 300 |
Solved! Go to Solution.
Hey @bdaodu ,
this measure
Measure 1 =
DIVIDE(
SUM('Table'[$] )
, CALCULATE(
SUM( 'Table'[$] )
, ALL( 'Table'[WBS Level 2] )
)
)
returns what you are looking for, for the 1st formula:
Use the formatting option
Hopefully, this provides what you are looking for.
Regards,
Tom
For 1. DAX formula to calculate $ for AA as % of total $ for each project.;
the expected result would be a table like this: using the followibg formula:
sum of AAs for project x/ sum of project x total
Project | WBS Level 2 | % |
X/9999 | AA | 70% |
X/9999 | BB | 39% |
X/9999 | CC | 0% |
X/9999 | DD | -9% |
X/10000 | AA | 24% |
X/10000 | BB | 33% |
X/10000 | CC | 7% |
X/10000 | DD | 9% |
X/10000 | EE | 27% |
Hey @bdaodu ,
this measure
Measure 1 =
DIVIDE(
SUM('Table'[$] )
, CALCULATE(
SUM( 'Table'[$] )
, ALL( 'Table'[WBS Level 2] )
)
)
returns what you are looking for, for the 1st formula:
Use the formatting option
Hopefully, this provides what you are looking for.
Regards,
Tom
Thank you!! It worked!
Hey @bdaodu ,
please mark an answer as solution, this will help others, looking for a similar solution.
Regards,
Tom
Hey @bdaodu ,
please provide the expected result and explain the calculation to get the result based on the sample data you provided-
Regards,
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |