Total values nor working correctly

[o59393]

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:

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:

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!

[Jihwan_Kim]

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

https://www.dropbox.com/s/waldisbkgvaakkz/Example%20PBIx.pbix?dl=0 

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

[o59393]

Hi @Jihwan_Kim 

Almost!

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

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!

[Jihwan_Kim · 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.

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

https://www.dropbox.com/s/waldisbkgvaakkz/Example%20PBIx.pbix?dl=0 

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

[o59393]

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!

[Jihwan_Kim]

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.

[o59393]

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!