Average of Semi-additive Sum in DAX

Question

I have a fact table that stores projects, companies that administered the projects and companies the projects should be billed to:

| Project | Company original | Company to display | Perc. |
| A       | ABC              | ABC                | 50%   |
| A       | ABC              | DEF                | 20%   |
| A       | ABC              | GHI                | 30%   |
| B       | DEF              | DEF                | 60%   |
| B       | DEF              | GHI                | 40%   |

Now I want to design a measure that works as follows:

• Within a project, sum the percentages
• Over projects, average the summed percentages (or MAX, which shouldn't make any difference, because all projects should add up to 100% anyway)

So if I use a Pivot Table from within Excel (2010) it will look like this:

| Project | Company | Perc. |
| A       | ABC     |  50%  |
| A       | DEF     |  20%  |
| A       | GHI     |  30%  |
| A (subtotal)      | 100%  |
| B       | DEF     |  60%  |
| B       | GHI     |  40%  |
| B (subtotal)      | 100%  |
| Total             | 100%  |

Notice that there's no time axis in this table - so the "regular" case for semi-additive measures (everything except time) doesn't work here.

Answer 1

This is pretty simple, indeed:

Sa = 
MAXX (
    VALUES ( Projects[Project] ),
    CALCULATE ( SUM ( Projects[Perc] ) )
)

Basically, you iterate over the projects and compute, at the project level, a simple SUM, which is then aggregated by MAX over different projects.