Trouble Creating (what I think should be a) Simple Measure

[jdballard30]

I have two tables in my source data (there's more, but those are the pertinent ones), Application and Transactions. An application can have one or many transactions. I have denormalized them into one table, ApplicationTransactions in Power BI. There is a column on the source Application table named EstimatedAmount. Because it's denormalized, EstimatedAmount gets repeated on every row because of multiple matching transaction rows. Here's my data:

ProgramName	ApplicationID	TransactionId	EstimatedAmount
Program1	Application1	Transaction1	125,000.00
Program1	Application1	Transaction2	125,000.00
Program1	Application2	Transaction3	300,000.00
Program1	Application3	Transaction4	152.50
Program1	Application3	Transaction5	152.50
Program1	Application3	Transaction6	152.50
Program1	Application3	Transaction7	152.50
Program1	Application4	Transaction8	279.30
Program1	Application5	Transaction9	63,910.22
Program1	Application5	Transaction10	63,910.22
Program2	Application6	Transaction11	17,000.00
Program2	Application7	Transaction12	25,000.00
Program2	Application7	Transaction13	25,000.00

I'm trying to create two measures for use in a chart like the one pictured below. The measures I'm trying to create are the Total Estimated Amount per application, and the Average Estimated Amount per application, like this:

ProgramName	TotalEstimatedAmount	AverageEstimatedAmount
Program1	489,342.02	97,868.40
Program2	42,000.00	21,000.00

I am trying to create the measure for the TotalEstimatedAmount and AverageEstimatedAmount. I know it has to be simple, but I can't figure it out.

There is a .pbix file with the above data on OneDrive here.

Thanks,

[Ashish_Mathur · Accepted solution]

Hi,

Try these measures

Measure1=SUMX(SUMMARIZE(VALUES(Data[ApplicationID]),[ApplicationID],"ABCD",MIN(Data[EstimatedAmount])),[ABCD])

Measure2=[Measure1]/DISTINCTCOUNT(Data[ApplicationID])

Hope this helps.