Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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.
Hi,
Try these measures
Measure1=SUMX(SUMMARIZE(VALUES(Data[ApplicationID]),[ApplicationID],"ABCD",MIN(Data[EstimatedAmount])),[ABCD])
Measure2=[Measure1]/DISTINCTCOUNT(Data[ApplicationID])
Hope this helps.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |