Forum Discussion

jenneferparr's avatar
jenneferparr
Helper I
6 years ago
Solved

If-Then Measure doesn't total correctly

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.

Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

8 Replies

    • jenneferparr's avatar
      jenneferparr
      Helper I
      6 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      jenneferparr
      Helper I
      6 years ago

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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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