Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hey,
Im looking for a way to get the sum of values for these :
Client | Material | Date | Value |
ABC | 101 | 12/1/2022 | 10 |
ABC | 102 | 13/1/2022 | 20 |
ABC | 101 | 13/1/2022 | 10 |
ABC | 103 | 13/1/2022 | 30 |
Is there a way for me to get the average value for each clients and sum it all ? Do note that there are tons of other clients with similar condition. Ideally it should take only from the latest date and average it but it works without latest date as well.
Thanks in advance !
Solved! Go to Solution.
Hi @Velvetine27
try to create a measure with the code below:
Hi @Velvetine27 ,
Is this what you want to achieve, calculate the sum and average of the corresponding ones based on the latest date and clients
The following are the relevant measure codes
Sum_value = SUMX(FILTER(ALL('Table'),'Table'[Client]=SELECTEDVALUE('Table'[Client])&&'Table'[Date]=SELECTEDVALUE('Table'[Date])),'Table'[Value])
Avg_value = AVERAGEX(FILTER(ALL('Table'),'Table'[Client]=SELECTEDVALUE('Table'[Client])&&'Table'[Date]=SELECTEDVALUE('Table'[Date])),[Value])
Best Regards,
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Velvetine27
try to create a measure with the code below:
wew this is the first time i see TREATAS. It works perfectly ! thank you for the solution and knowledge imparted.
With ADDCOLUMNS, we build intermediate tables with expected granularity, but the data lineage is broken. TREATAS serves to rewire the relationship with the original table.
Hey sorry I just noticed something. This measure ignores the data that isn't available at MAX(Date). Some clients only have data until middle of the month so this measure seems to ignore those that are not available at the MAX(Date). Is there a way we can get the latest date instead ?
the MAX(Date) is inside ADDCOLUMNS, calculated based on the row context from VALUES([Client]), so this shall not be an issue. Could you say more about the issue?
sum of the average normally doesn't make any sense. what is the purpose?
And what does this mean "works without latest date as well"?
The idea is that we are looking for Value for Client. And the Value for Client is the average for all the Values of Materials under the Client. Thus why I'm looking for the Average to get the Client's value. But at the same time I need to sum all of the Average Values for all Clients.
The Date by right it should take only the latest date of the month. But if we average the value for entire month I assume it wouldnt affect much the Average Value in the end. Sorry if my explanation doesn't make sense.
average over multiple monthly ends?
would suggest your provide more data, so the advisor could verify the proposed solution.
Yes, the aggregation should take the final date. So if we display monthly data it should provide latest monthly data for each Client.
Client | Material | Date | Value |
ABC | 101 | 1/1/2022 | 10 |
ABC | 102 | 5/1/2022 | 20 |
ABC | 101 | 25/1/2022 | 10 |
ABC | 103 | 25/1/2022 | 30 |
BCD | 104 | 13/1/2022 | 40 |
BCD | 104 | 21/1/2022 | 40 |
BCD | 105 | 24/1/2022 | 50 |
BCD | 106 | 24/1/2022 | 60 |
Will this sample data be enough ? The calculation should take :
for ABC, latest date 25/1/2022 = (10 + 30) / 2
for BCD, latest date 24/1/2022 = (50 + 60) / 2
final value = 20 + 55
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |