Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sudhavi_84
Helper V
Helper V

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?

 

Please help.

2 ACCEPTED SOLUTIONS

Hi,

Write this measure

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hey @Ashish_Mathur ,

 

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

 

 

View solution in original post

28 REPLIES 28
Sudhavi_84
Helper V
Helper V

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

Sudhavi_84
Helper V
Helper V

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.

Sudhavi_84
Helper V
Helper V

Thanks @v-janeyg-msft 

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

 

 

Sudhavi_84
Helper V
Helper V

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

 

v-janeyg-msft
Community Support
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.

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.

 

v-janeyg-msft_0-1623141793171.png

 

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

Hi @v-janeyg-msft 

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

 

Hi @v-janeyg-msft 

 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

 

Hi @v-janeyg-msft 

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]
)
 
appears to be not working. Please help

Hi @v-janeyg-msft 

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur  and @v-janeyg-msft 

I am trying to acheive Grand total figure here in below highlighted visual.

Sudhavi_84_0-1623655259504.png

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.

Sudhavi_84_1-1623656755135.png

 

 

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

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.