Forum Discussion

PierreL's avatar
PierreL
Frequent Visitor
6 years ago
Solved

How to get the correct total from IF Measure

Hello,

I have a measure that returns the volume sold (OTBRN) based on a date selected in a slicer (last update daily). If the return value is blank, it returns the volume sold as of the MAX date available in the Last update column. The measure is working correctly except that the total is not adding all the values. It seems it is only adding the values found if value_if_false. Is there a way to have the total including all?

 

Measure code:

 

 

OTB As of = IF(ISBLANK(CALCULATE(
    SUM ( Daily[OTBRN]),
    FILTER (
         Daily,
         Daily[Last update] = MAX('Updates'[Last update daily])
    )
)),
CALCULATE(
    SUM ( Daily[OTBRN]),
    FILTER (
         Daily,
         Daily[Last update] = MAX(Daily[Last update]))
    )
,
CALCULATE(
    SUM ( Daily[OTBRN]),
    FILTER (
         Daily,
         Daily[Last update] = MAX('Updates'[Last update daily])
    )
))

 

Many thanks for the suggestion.

Need Help
  • PierreL's avatar
    PierreL
    6 years ago

    Hello,

     

    So after doing some research I have a solution for this problem to do a measure to have the correct SUM.

     

     

    Measure = 
SUMX( VALUES( (Daily[Date] )), CALCULATE([OTB As of]))

     

     

    Thanks all for your help. 

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    As you are comparing to max value. The Grand total will be recalculated, so force a group on the line total

    sumx(summarize(Table,table[Id1],Table[D2],"_sum",[OTB As of]),[_sum])

     

    Here ID1 or ID 2 can be one or more. Thay can be a date, Month, etc. What you are viewing in the rows.

    • PierreL's avatar
      PierreL
      Frequent Visitor
      6 years ago

      Many thanks for the quick reply.

       

      I tried it as below however the result is the SUM of all dates and I cant seem to have only the date filtered as done in the [OTB as of] measure. I think the problem is that in SUMMARIZE I can only have columns from the table DAILY and I would need to add another parameter coming from a different table. 

       

      Measure = SUMX(SUMMARIZE(Daily,Daily[OTBRN],Daily[Last update],"_SUM",[OTB As of]),[_SUM])
       
      Thanks again for your help.
      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        PierreL 

        If the measure is coming from a single table, you can take that table and dimensions joined that table

         

        Summarize(fact, dim1[D1], dim1[d2],dim2[Dt1]....)

         

        In the case of multiple tables/fact, we have to use dimension.

        Summarize(Dim1,1 Dim[D1],fact1[Meausre], fact2[Meaure]...

         

        See if this can help

         

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi PierreL ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,

     

    • PierreL's avatar
      PierreL
      Frequent Visitor
      6 years ago

      Hello,

       

      So after doing some research I have a solution for this problem to do a measure to have the correct SUM.

       

       

      Measure = 
SUMX( VALUES( (Daily[Date] )), CALCULATE([OTB As of]))

       

       

      Thanks all for your help.