cancel
Showing results for
Did you mean:
Resolver I

## Measures Is working perfect but total value is not correct

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?

2 ACCEPTED SOLUTIONS
Super User III

Hi,

Write this measure

Measure = SUMX(VALUES(Milestone[Project]),[My Measure])
Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

Hey @Ashish_Mathur ,

Your measure worked for me, Thank you. How can I use average for it for same column please?

28 REPLIES 28
Resolver I

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

Resolver I

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.

Resolver I

Thanks @v-janeyg-msft

I will try to find a way to upload it via url links and let you know.

Resolver I

Community Support

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

Community Support

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.

Resolver I

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?

Community Support

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

Resolver I

Hi I have PBI ready. Please May I know, how and where should I attach the file?

Community Support

@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

Resolver I

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

Resolver I

Please could you help me on this. I will upload the PBI with three tables and measures too.

Community Support

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

Resolver I

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.

Community Support

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

Resolver I

I used this measure below as you specified.

cost =
SUMx (
SUMMARIZE (
Mstone,
Mstone[Project Level Date],
Mstone[Project Level],
Mstone[Desc],
Mstone[Project],
"Cost",
VAR ABC1 =
CALCULATE (
MAX ( Mstone[Project Level Date] ),
Mstone[Project Level] = "ABC",
Mstone[Desc] = "Current"
)
VAR XYZ1 =
CALCULATE (
MAX ( Mstone[Project Level Date] ),
Mstone[Project Level] = "XYZ",
Mstone[Desc] = "Current"
)
VAR a =
SELECTEDVALUE ( Mstone[Project] )
VAR Result =
CALCULATE (
SUM ( Vowd[Cost] ),
FILTER ( Vowd, Vowd[Per] >= ABC1 && Vowd[Per] <= XYZ1 ),
Master[Projects] = a,
Vowd[Desc] = "Current"
)
RETURN
Result
),
[Cost]
)

Resolver I

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:-)

Super User III

Hi,

In simple English, could you please let me know what exact result are you expecting.  Please also explain the question.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

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.

Super User III

Hi,

Write this measure

Measure = SUMX(VALUES(Milestone[Project]),[My Measure])
Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements