Forum Discussion
DAX formulas
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 |
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 optionHopefully, this provides what you are looking for.
Regards,
Tom
5 Replies
Hey bdaodu ,
please provide the expected result and explain the calculation to get the result based on the sample data you provided-
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 optionHopefully, this provides what you are looking for.
Regards,
Tom
Thank you!! It worked!
