cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Silver75 Regular Visitor
Regular Visitor

How can I multiply Measure per Column?

Hi everyone,

I'm building a report table similar to example

example.jpg

 

I need a new measure

new measure = Measure*Column.

 

Could you help me please?

thank you

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: How can I multiply Measure per Column?

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. Smiley Happy

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

6 REPLIES 6
Super User
Super User

Re: How can I multiply Measure per Column?

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


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

Proud to be a Datanaut!


Silver75 Regular Visitor
Regular Visitor

Re: How can I multiply Measure per Column?

HI @Greg_Deckler

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

Super User
Super User

Re: How can I multiply Measure per Column?

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.


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

Proud to be a Datanaut!


v-ljerr-msft Super Contributor
Super Contributor

Re: How can I multiply Measure per Column?

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. Smiley Happy

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

vmakhija Member
Member

Re: How can I multiply Measure per Column?

@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

Highlighted
rtrohitsingh Frequent Visitor
Frequent Visitor

Re: How can I multiply Measure per Column?

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.