Forum Discussion

Silver75's avatar
Silver75
Frequent Visitor
9 years ago
Solved

How can I multiply Measure per Column?

Hi everyone, I'm building a report table similar to example   I need a new measure new measure = Measure*Column.   Could you help me please? thank you  
Need Help
Tips and Tricks
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Silver75,

    The formula is ok but the grand total it's wrong

    Based on my experience, using SUMX function could make it work. The formula below is for your reference. :smileyhappy:

    New measure = SUMX ( 'TableNameOfProductDim', [Measure] * SUM ( [Column] ) )

    Note:  replace 'TableNameOfProductDim' with the real table name of your Product Dim table which contains a column of individual value of ID PRODUCT.

     

    Regards

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Is your "Measure" in your example a column or a Measure? If you need an actual measure, you will need to use an aggregation function like MAX, MIN or SUM like:

 

MyMeasure = SUM([Measure])*SUM([Column])

Otherwise, if Measure is an actual measure, then you shouldn't need the SUM for [Measure].

Silver75's avatar
Silver75
Frequent Visitor
9 years ago

HI Greg_Deckler

thank you for your solution.  Measure is an actual measure. The formula is ok but the grand total it's wrong

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Yeah, that is not uncommon with measures. You generally have to do an IF with a HASONEFILTER or something along those lines to get the grand total to calculate correctly. Essentially, you can think of a measure in a grand total as your measure calculating in the context of ALL.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Greg_Deckler

      In case my user is free to use whatever field they want, how does it work then?

      I mean I can't put HASONEFILTER for multiple columns as such.

      Any suggestions?

       

      Regards

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Silver75,

    The formula is ok but the grand total it's wrong

    Based on my experience, using SUMX function could make it work. The formula below is for your reference. :smileyhappy:

    New measure = SUMX ( 'TableNameOfProductDim', [Measure] * SUM ( [Column] ) )

    Note:  replace 'TableNameOfProductDim' with the real table name of your Product Dim table which contains a column of individual value of ID PRODUCT.

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Get the second sum forumla out of there. That makes it calculate incorrectly

    • RZAU's avatar
      RZAU
      Frequent Visitor
      3 years ago

      In my senario, [Measure]*Max([Column]) is the best option. 

  • agostinagranja's avatar
    agostinagranja
    New Member
    3 years ago

    You should make another measure containing the sum of the column

    Measure2 = SUM([Column])

    and then use it in the final measure like this:

    New measure = SUMX ( 'TableNameOfProductDim', [Measure] * [Measure2] )