Forum Discussion

jdballard30's avatar
jdballard30
Icon for Helper II rankHelper II
7 years ago
Solved

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

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:

 

 

ProgramNameApplicationIDTransactionIdEstimatedAmount
Program1Application1Transaction1125,000.00
Program1Application1Transaction2125,000.00
Program1Application2Transaction3300,000.00
Program1Application3Transaction4152.50
Program1Application3Transaction5152.50
Program1Application3Transaction6152.50
Program1Application3Transaction7152.50
Program1Application4Transaction8279.30
Program1Application5Transaction963,910.22
Program1Application5Transaction1063,910.22
Program2Application6Transaction1117,000.00
Program2Application7Transaction1225,000.00
Program2Application7Transaction1325,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:

 

ProgramNameTotalEstimatedAmountAverageEstimatedAmount
Program1489,342.0297,868.40
Program242,000.0021,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,

  • Hi,

     

    Try these measures

     

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

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

     

    Hope this helps.

1 Reply

  • Hi,

     

    Try these measures

     

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

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

     

    Hope this helps.