Total values incorrectly returning 0

[o59393]

Hi all

I have created some if selected dax mesaures, they work perfectly when you select the respective value (project type).

The only problem I have is when you select all the project types, the total value will return me a 0.

For example, I select a project and totals work well:

If no filter applied, then I see 0's:

How can I get the totals to work right?

Thanks!

Pbix:

https://1drv.ms/u/s!ApgeWwGTKtFdh0kvvkR3y-W9v05l?e=WuL1M6

[Jihwan_Kim · Accepted solution]

Hi, @o59393 

Please try the below measure for the TOTAL FIX.

The link is down below.

If the number is incorrect, please let me know what number you want to see.

Final Cost Tons total fix =
SUMX (
CROSSJOIN ( 'Query1 (3)', VALUES ( 'Productivity Central'[Project type] ) ),
[Final Cost Tons]
)

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]

Wow @Jihwan_Kim 

That worked. I had never seen that function before (crossjoin). Can you explain briefly the logic of it and why using values too was needed to get the totals?

I used the same measure for Inicial Cost Tons and worked well:

Thank you so much!

[Jihwan_Kim]

Hi, @o59393 

Thank you for your feedback.

I might need to step further to optimize the measure but the step before optimizing the measure, I generally try to create the virtual table like this if there is a problem on the TOTAL row.

For instance,

If your initial measure has a if / max / min / selectedvalue / lastnonblank / and so forth / , it generally works row by row. Because the condition in the measure is considering row by row. However, when it comes to the Total row or the sub-total row, the measure no longer can consider row by row. It considers the whole. And in most cases, selectedvalue / min / max is returning by referring to the whole, not row by row.

So, in order to force the row-by-row-consideration on the Total Level as well, the iteration function (in this case, SUMX() ) can be helpful.

The iteration function needs to refer to the actual table (or virtual table).

If you do not have the slicer on the top, only referring to the actual table is fine. However, if you also want the slicer to correspond with the measure, the virtual table, that is created by crossjoin or any other similar dax function, is needed.

Of course, if the slicer is coming from the same table, just referring to the actual table is enough.

I hope I explained well.

[o59393]

Hi @Jihwan_Kim 

I created a new table like this with your formula:

Is it some kind of virtual table your measure the sums row by row all the project types?

Regards!

[Jihwan_Kim]

Hi, @o59393 

I think it is.

One more thing is that the reason why I used the whole table Query1 (3) is that I could not find the primary-key-column in the table. If you know which column is the primary-key-column, then you can also try something like below.

Total Fix =
SUMX (
CROSSJOIN ( VALUES ( [primarykeycolumn] ), VALUES ( [projecttypecolumn] ) ),
[your measure]
)

Then, I think, your virtual table becomes much slimmer.

[Portrek]

Hi.

It is returning zero because it was the value that you determined as final in its measure..

Put a function sum from your table/collumn and solve the problem.

Beast regards.

[o59393]

Hi @Portrek 

I removed the 0, and placed this in the false statement instead. It didnt work either

    IF(
    SELECTEDVALUE(
        'Productivity Central'[Project type])="Reduccion",

SUMX(
    SUMMARIZE(
        'Query1 (3)','Query1 (3)'[Merged],
        "Kg Inicial",[Valor Inicial],
        "Liters AC",[Liters AC],
        "Cost Inicial",MAX('Productivity Central'[Valorinicial(CostoporTONde]
                )
            ),
        [Kg Inicial]*[Liters AC]*[Cost Inicial]
    )
)
+
IF(
    SELECTEDVALUE(
        'Productivity Central'[Project type])="Aligeramiento",
SUMX(
    SUMMARIZE(
        'Query1 (3)','Query1 (3)'[Merged],
        "Kg Inicial",[Valor Inicial]/1000000,
        "Individual Units AC",Sum('Query1 (3)'[Units]),
        "Cost Inicial",MAX('Productivity Central'[Valorinicial(CostoporTONde]
                )
            ),
        [Kg Inicial]*[Individual Units AC]*[Cost Inicial]
    )
+
IF(
    SELECTEDVALUE(
        'Productivity Central'[Project type])="Empaque",
SUMX(
    SUMMARIZE(
        'Query1 (3)','Query1 (3)'[Merged],
        "Kg Inicial",[Valor Inicial],
        "Individual Units AC",Sum('Query1 (3)'[Units])
            ),
        [Kg Inicial]*[Individual Units AC]
    )

Same for the measure Final Cost Tons total fix

Can you help how it should really be?

Thanks.