What I am trying to do is I have 2 tables
Table 1 has project, project level and dates
Table 2 has projects, cost and Dates
Table1 Projects may not be in Table 2 projects
I am trying to write the retrieve the sum of cost based on Dates for each project in both tables.
VAR ABC1 = CALCULATE(MAX(Table1[Date]),Table1[ProjectLevel1] = "ABC",Table1[Desc]="Current")
VAR XYZ1 = CALCULATE(MAX(Table1[Date]),Table1[ProjectLevel1] = "XYZ",Table1[Desc]="Current")
var a = SELECTEDVALUE(Table1[Project])
VAR Result = CALCULATE(SUM(Table2[Value]),FILTER(Table2,Table2[Period] >=ABC1 && Table2[Period] <=XYZ1),Table3[Project]=a,Table2[Desc]="Current")
Above measure is working perfectly for me.
What I am trying to acheive here is Total as well by adding below measure
RETURN IF(a<>BLANK(),Result,CALCULATE(SUM(Table2[Value]),FILTER(Table2,Table2[Period] >=ABC1 && Table2[Period] <=XYZ1),Table2[Desc]="Current"))
But total value is not accurate because it is chosing Table 1 Max date of the project,is not listed that project in Table 2.
So with this total is coming not accurate.
Basically, it should sum only if matching projects from both tables and choose between max date and sum up.
How can I acheive this in Total?
Solved! Go to Solution.
Write this measure
I posted (https://community.powerbi.com/t5/Desktop/MedianX-is-not-working-Please-Help/m-p/1908928#M730615) link as new issue. Please any chance to look in to it. Last time you helped me achieving average but when I am using same thing to acheive median, it's not working. Please help
Thank you for this I finally got what I need.
Last question if possible, To acheive this total sum, we are adding other measure and referring from existing measure. With this we having 2 new measures to get the sum.
How can we do this in single measure rather than 2 measures please.
I'm a little busy yesterday, sorry for not responding in time. I try to check your sample file, but the data model is too big, I can't find which measure and which table you asked, and my computer has crashed twice. .so can you sort out the relevant data instead of all data, otherwise it will be difficult for me to help you. Thanks. And you can delete the above link for privacy .
You can try to use summarize function.
cost = SUMX ( SUMMARIZE ( Table1, [Date], [ProjectLevel1], [Desc], [Project], "Cost", VAR ABC1 = CALCULATE ( MAX ( Table1[Date] ), Table1[ProjectLevel1] = "ABC", Table1[Desc] = "Current" ) VAR XYZ1 = CALCULATE ( MAX ( Table1[Date] ), Table1[ProjectLevel1] = "XYZ", Table1[Desc] = "Current" ) VAR a = SELECTEDVALUE ( Table1[Project] ) VAR Result = CALCULATE ( SUM ( Table2[Value] ), FILTER ( Table2, Table2[Period] >= ABC1 && Table2[Period] <= XYZ1 ), Table3[Project] = a, Table2[Desc] = "Current" ) RETURN Result ), [Cost] )
If it doesn't solve the problem, Please feel free to ask me.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
First of all, thank you very much for letting me know.
I used your summarise function, I am getting only values for 3 Projects (One of the project value is correct but other 2 were wrong) and also supposed to get the values for other projects too..Not sure what is missing here. Any help?
If you need further help, I need to know your data, because your code contains three tables and uses a lot of columns. It is difficult for me to see where the problem is without data. Can you share some sample fake data and your desired result like?
@Sudhavi_84 Can you upload a link on onedrive for business? Or, you can share the data in the form of tables and the results in the form of screenshots.
Thank you, Can I share the PBI via My Drive? I have it my "My Drive". Will it be ok If I share with your Email ID?
Could you please let me know once you got the link. I will restrict it again. Its just dummy data anyways with good model
I've been waiting for you, you just need to put the issue to express simple but complete, so we can help you soon.
I am sorry I don't have One Drive but would it be ok if I upload via Google doc or Dropbox? Just made PBIX file ready so that you can have a look please?
I am struggling to attach here as I don't have option to attach it.
Thank you in Advance
We can't recommend you to use tools other than onedrivefor business, sorry.
Because of insufficient permissions, you can only upload files by sending url links.
I used this measure below as you specified.
Hi I uploaded in one Drive. I updated your measure too.
Please let me know if the below link not working
So what I am trying to acheive here is Correct total and Average please.
Thank you very much for you help:-)
In simple English, could you please let me know what exact result are you expecting. Please also explain the question.
I am trying to acheive Grand total figure here in below highlighted visual.
If you see the sum of individual projects they are coming accurate. Criteria is to pick the Project and Project level, and project level date from Table 1 and it should get the cost from Table 2, by summing the cost with in the Project and in between the dates.
For example Project A123.20, has date of 5th April 2021 for XYZ and and 14nth June 2021 for ABC. It is suming the value from Table2 between those dates for that Project ID.
But for total sometimes coming incorrect because it should be be sum of cost between the projects which are in both tables.
I have Project ABCD in Table1 but that project is not in Table2 with this Grand total sometimes not coming accurate. Total should be Matching projects from both tables and if matching pick the ABC and XYZ max date and get the total.
Thanks in advance.
Write this measure
Check out the News & Announcements to learn more.
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.