Total scope per project and for all projects

[Zakaria_1980]

Dears,

i've a table like this below:

based on the above data, i'd like to show me in my PBI report the following:

if I select project 1, total scope will be 78

if I select project 2, Total scope will be 100

if i select project 1 & 2, total scope will be 78+100

if I select all projects, it will be the sum of non dupplicated scopes

Thanks for your urgent support.

br,

[parry2k · Accepted solution]

@Zakaria_1980 add this measure

Total =
SUMX ( VALUES ( Table[Project Name] ), CALCULATE ( MAX ( Table[Total Scope] ) ) ) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Zakaria_1980 add this measure

Total =
SUMX ( VALUES ( Table[Project Name] ), CALCULATE ( MAX ( Table[Total Scope] ) ) ) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Zakaria_1980]

can you explain  what does this funtion do please. i need to understand it.

Thanks

[parry2k]

@Zakaria_1980 sure, here is the breakup and if you need further information, please let me know.

Total =
SUMX ( --iterate over each row of the table and first parameter is a table, ans sum the value returned by 2nd parameter
VALUES ( Table[Project Name] ), --this is a table on which sumx will iterage, VALUES function will return the unique list of project names (in other words, table of unique project name)
CALCULATE ( MAX ( Table[Total Scope] )  --max function will return the MAX value of each project name, and we need to use CALCULATE the row filter context to get the max value
) 
) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Zakaria_1980]

Please what will be the formula if the first iteration will be per project and sencond iteration will be per Milestone to get total scope

below a snapshot of part of my Database, and what i want is the folloiwng:

If I Select project 1 and Pac signed as Milestone it should give me : 70

If I select All projects and pac signed as Milestone it should give me: 242

If I select Project 1 and different from pAc signed  as Miletsone it should give me: 27

if I select all projects and different from pac signed as Milestone it should give me: 62

If I select project 2 and pac signed as Milestone it should give me: 172

If I select project 2 and different from Pac signed as milestone it should give me: 35

thanks for your support with the measure that i have to put in place.

[parry2k]

@Zakaria_1980 try this

Total =
SUMX ( 
SUMMARIZE( Table, Table[Project Name], Table[Milestone], "__max", MAX ( Table[Total Scope] ) ),
[__max]
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Zakaria_1980]

Hi,

for pac signd milestone, i checked the box pac signed in the filetr (see belw snapshot) to get total scope for Pac signed Milestones either per 1 project or multiple projects, which works fine.

how can I do to get total scope for all other Milestones except pac Signed , what shall I put in the filter or under the mesure to work ?

[parry2k · Accepted solution]

@Zakaria_1980 select all options and unselect PAC Signed