If-Then Measure doesn't total correctly

[jenneferparr]

I have a table where I've created 3 measures to calculate a 4th measure using an if statement. I have two problems but specifically need help getting that if statement to total correctly.

Here are my measures:

NET_TON = SUM(T_LOADS[NET])/2000

NetTonToMBF = SUM(T_LOADS[NET])/2000 / SUM(T_UOM_CONVERSION[MULTIPLIER])

BF_Net = SUM(T_LOAD_DETAIL[VOLUME_NET]) + SUM(T_LOAD_DETAIL[VOLUME_UTILITY])

CALCBF = IF (

T_LOAD_DETAIL[BF_Net]) = 0,

T_LOADS[NetTonToMBF]*1000,

T_LOAD_DETAIL[BF_Net]

)

All of the measures calculate correctly at the row level, but the CALCBF measure doesn't total correctly. Here is what the matrix looks like:

As you can see, the CALCBF column is only totalling the Sawlog product ID (plus a 4th product ID that doesn't show, which is a separate, less important, problem). I need that column to give me a total for all the product IDs, but I can't for the life of me figure out what I'm missing in my measures. The only filters applied are on Date and Status. I've tried replacing all the SUM's with SUMX as I've seen in every Google result on this issue, but it doesn't work at all in my situation.

[harshnathani]

Hi @jenneferparr ,

Try this measure

CALCBF =
SUMX (
    'T_LOAD_DETAIL',
    IF (
        T_LOAD_DETAIL[BF_Net] = 0,
        T_LOADS[NetTonToMBF] * 1000,
        T_LOAD_DETAIL[BF_Net]
    )
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[jenneferparr]

Thanks @harshnathani, I tried that and it removed the row totals for all but one product ID. 

[harshnathani]

Hi @jenneferparr ,

Can you share the entire screen shot.

Not very clear as to what happened.

Also share a sample file if possible.

Regards,

HN

[jenneferparr]

Here is the full matrix:

and here is the measure with your changes:

Maybe I mistyped something? Unfortunately I can't share the file for confidentiality reasons. 

The two tables included in these measures are related one-to-many on LOADID.

[v-xicai · Accepted solution]

Hi @jenneferparr ,

You may create a new measure like DAX below, assuming the field in Rows box of Matrix visual is Table1[Product ID].

CALCBF_New =
VAR _table =
    SUMMARIZE ( Table1, Table1[Product ID], "_Value", [CALCBF] )
RETURN
    IF ( HASONEVALUE ( Table1[Product ID] ), [CALCBF], SUMX ( _table, [_Value] ) )

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jenneferparr]

Thank you, @v-xicai ! That solved it!

[Pragati11]

Hi @jenneferparr ,

Try checking this existing thread for this:

https://community.powerbi.com/t5/Desktop/Measure-Total-not-reflecting-correctly/m-p/668833

Thanks,

Pragati

[jenneferparr]

Hi @Pragati11,

I did look at that post. It doesn't help me figure out which of my measures needs to be adjusted or how. I've also read through all the other posts that are related to this problem, and followed all the links provided in those posts.