cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
o59393
Post Prodigy
Post Prodigy

Total values nor working correctly

Hi all

 

I created a few measures to calculate the total costs (initial and final) and savings (difference between final and initial), however I am not getting the right values as seen below:

sdsad1g.png

 

The values surrounded in red is giving a total of zero and the values surrounded in black is giving $32,297 MM (same as the Cost tons initial) which both are incorrect.

 

For the second table above, I am filtering by brand "XXX".

 

The correct values should be the ones when I export the second table:

 

fdsf0.JPG

 

I tried combining summarize + sumx in my cost formulas, but it doesnt seem to work well.

 

How can I get my measures to work?

 

I attach pbix

https://1drv.ms/u/s!ApgeWwGTKtFdh0fzZlafmlA_W5kY?e=jUjru0

 

Thanks all!

1 ACCEPTED SOLUTION

Hi, @o59393 

Thank you for your feedback.

I am not sure whether I am proceeding to the right path, but please check the below picture and the link.

 

Picture1.png

 

Cost Tons final total fix = SUMX('Query1 (3)', [Cost Tons final])
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

6 REPLIES 6
Jihwan_Kim
Super User
Super User

Hi, @o59393 

I am not sure whether I understood your question correctly, but please try the below measure.

 

Savings = SUMX( 'Query1 (3)',[Cost Tons initial] - [Cost Tons final])
 
Picture1.png
 
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Hi @Jihwan_Kim 

 

Almost!

 

The only thing not working is the total for "Cost Tons Final"

 

4324dsf.JPG

 

The correct total should be = $31.405 millions (you can export the table to csv to verify)

 

I dont know why is summing incorrectly, but it seems to be taking the false statement and not the true one.

 

Thanks!

Hi, @o59393 

Thank you for your feedback.

I am not sure whether I am proceeding to the right path, but please check the below picture and the link.

 

Picture1.png

 

Cost Tons final total fix = SUMX('Query1 (3)', [Cost Tons final])
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Hi  @Jihwan_Kim 

 

Thanks for the help! your new measure gave the right value. Would it be possible to have one measure (Cost Tons final in this case) instead of 2? In order to make it more efficient. 

 

I can't understand why I am not getting the same value as your Cost Tons final total fix 😞

 

The logic of the measure Cost Tons final is simple , if Query1 (3)'[Date] >= Real start date then [Kg final]*[Liters AC]*[Cost Final], else use Cost Tons initial

 

Thanks!

 

Hi, @o59393 

Thank you for your feedback.

I am not very familiar with your whole data model, but what I can briefly explain is that,

 

- when a measure includes some conditions for calculation, for instance, if / min / max /... , in a table visualization, row by row seems to work because the condition itself is applied to row by row. However, when it comes to the total line, sometimes it does not show as I expected. The measure is still working properly, but the result I wanted is a different thing on the total line.

Because the condition in the measure is applied to the whole data (not row by row) when it comes to the total line.

There are some ways to force the measure to consider row by row always, even it comes to the total line. In this case, using sumx is one of many ways to make the measure to be operated in the way I want.

And when using sumx to fix the measure, the easiest way is to create one more measure. I could not try to make two into one measure because I have a lack of full understanding of the data model and the business. Sometimes it can be done by just adding sumx into the same measure, but sometimes it needs to create a virtual table inside the measure to force the measure to work in a way I want. in that case, a measure becomes much longer than I expected.

I am not 100% sure for every case, but in my opinion, writing one more measure like what I have suggested is not influencing too much on the performance. 

I hope it helps.

Thanks.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Thanks for the explanation @Jihwan_Kim 

 

In fact I tried to add the sumx following the return on the "Cost tons final" measure

 

    Cost Tons final = 
    var _final = 
    SUMX(
        SUMMARIZE(
            'Query1 (3)','Query1 (3)'[Country],'Query1 (3)'[Plant],'Query1 (3)'[Brand],'Query1 (3)'[Package],
            "Kg final",[Kg/L Final],
            "Liters AC",[Ltr AC],
            "Cost Final",MAX('Productivity Central'[Valorfinal(CostoporTONdeaz]
                    )
                ),
            [Kg final]*[Liters AC]*[Cost Final]
        )
    var _initial = [Cost Tons initial]

    Return

    SUMX(
        'Query1 (3)',
    IF(
        SELECTEDVALUE(
            'Productivity Central'[Real start date])<=SELECTEDVALUE('Query1 (3)'[Date]),
                _final,
                _initial
    )
    )

 

But it gave a huge total  of = $3 billions hehe

 

So as you say the easiest way would be to create a new measure.

 

Thanks!

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!