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.
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.
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
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?
Please help.
Solved! Go to Solution.
Hi,
Write this measure
Hey @Ashish_Mathur ,
Your measure worked for me, Thank you. How can I use average for it for same column please?
Hi
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.
Please could you confirm when you get the link?
Hi, @Sudhavi_84
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 .
Reference:
How to Get Your Question Answered Quickly - Microsoft Power BI Community
Best Regards
Janey Guo
Hi, @Sudhavi_84
You can try to use summarize function.
Like this:
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.
Best Regards
Janey Guo
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?
Hi, @Sudhavi_84
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?
Best Regards
Janey Guo
Hi I have PBI ready. Please May I know, how and where should I attach the file?
@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.
Best Regards
Janey Guo
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?
https://drive.google.com/file/d/1tYmgSida8xfVn6Mk8BZ4lU51MHW8Zfrh/view?usp=sharing
Could you please let me know once you got the link. I will restrict it again. Its just dummy data anyways with good model
Please could you help me on this. I will upload the PBI with three tables and measures too.
Hi, @Sudhavi_84
I've been waiting for you, you just need to put the issue to express simple but complete, so we can help you soon.
Best Regards
Janey Guo
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
Hi, @Sudhavi_84
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.
Best Regards
Janey Guo
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
https://1drv.ms/u/s!An2H1J0iSc93bKntBIomFkPVqBg?e=L3lOmP
So what I am trying to acheive here is Correct total and Average please.
Thank you very much for you help:-)
Hi,
In simple English, could you please let me know what exact result are you expecting. Please also explain the question.
Hi @Ashish_Mathur and @v-janeyg-msft
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.
Hi,
Write this measure
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |