GROUPBY or CALCULATE or new table?

[Anonymous]

Hi PowerBI community,

I hope all of you are safe in these challenging times.

I have a problem which I'm not able to solve as I can't figure out what the right approach is. I have a dataset with columns that have been unpivoted (for a good reason) but that lay the information out in the wrong format for one particular calculation I'm trying to run. An example of the current table is as follows (important to note that the approved budget is actually for whole year but when unpivoted is showed for each line)

Program	Project	Date	Approved Budget
Program 1	Proj A	01-Jan-2020	20
Program 1	Proj A	01-Feb-2020	20
Program 1	Proj A	01-Mar-2020	20
Program 1	Proj B	01-Jan-2020	10
Program 1	Proj B	01-Feb-2020	10
Program 2	Proj D	01-Jan-2020	50
Program 2	Proj D	01-Feb-2020	50

The output I'm seeking through a magic measure would be (with the ability to have slicers on Program and Project or also do add different layers in the table and have the calculation done at the project level):

Program	Approved Budget
Program 1	30
Program 2	50

Thanks in advance for any suggestions 🙂

OF

[amitchandak · Accepted solution]

@Anonymous 

Create a new measure like

sumx(summarize(table, table[Program],table[Project], "_max",max(Approved Budget)),[_max])

[Anonymous]

Thanks very much, it's perfect 🙂