How can I multiply Measure per Column?

[Silver75]

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

[Anonymous]

Is this resolved. If so, how did you do it?

[Greg_Deckler]

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].

[Anonymous]

Hi Greg,

I have used the same formula to create Total Cost. I have used formula : 

Total Cost = SUM(Sheet1[QtyShip])*sum(Sheet1[CuryCost])

But Total Visualizaion Total Amount is showing wrong value. I could not able to insert image.

[Silver75]

HI @Greg_Deckler

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

[agostinagranja]

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] )

[v-ljerr-msft · Accepted solution]

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. 

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

[RZAU]

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

[Anonymous]

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

[Greg_Deckler]

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]

@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