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,
It worked very well yesterday when i tried it. I could see the sum at the bottom. I cannot download your file now (there is no file at the link you shared earlier) and show the screenshot to you. Please retry.
Hey @Ashish_Mathur ,
Your measure worked for me, Thank you. How can I use average for it for same column please?
Hi,
You are welcome. Replace SUMX() with AVERAGEX()
Please Let me know if you still cant view it.
I just created new link.
Please could you also help me on acheiving Average please? Total cost/count of projects which has cost.
Thanks Ashish for helping me on this
Please any help on this. I am really struggling for this measure. May be I am doing grouping wrong? or measure wrong? not understanding what I am missing here:-(
Issue I am having is not able to get Grand Total and also please if possible can I get the Average as well like Sum of all projects cost/total number of projects.
I am still trying to figure it out how it can be acheived.
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |