cancel
Showing results for
Did you mean:
Helper I

## multiply column and a measure - wrong total

Hi There,

i am trying to multiply the total quantity of the items with the number of orders (frequency) but in second screenshot total is 190 instead of 86 (64+22)

can you please help to fix that total, i have spent 2 days and couldnt figure out the error.

best regards

tim

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
12 REPLIES 12
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi Ashish,

i modified it a bit as it was connected to the database.

TEST- Copy.pbix

but ultimately what i want to do is to divide "measure3" per line to grand total of "measure" (which is wrong or not what i want in the file)

i want to see 31,214,080 instead of 5,221,978,332

hope that make sense

timur

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi Ashish,

Total is correct thank you, Is it possible to find weighted average of these?

Measure 2 per item / total measure 2

that would be Legendary!

Super User

Measure 3 = DIVIDE([Measure 2],CALCULATE([Measure 2],ALL(Sheet3[No_])))

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi Ashish,

I will need one more thing, do i need to open a new post for it?

As this is a measure (and not a column) i am struggling to get the cumulative % of measure 2, is this something you can help with?

Super User

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Absolute Legend!!

thank you very much Ashish

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

I will need one more thing, do i need to open a new post for it?

As this is a measure (and not a column) i am struggling to get the cumulative % of measure 2, is this something you can help with?

Solution Sage

Technically, your measure is doing what you've asked it to do (multiply the value in the quantity by the value in the frequency), and this applies to the total row too. Of course, this isn't the result you want. Greg Deckler has a very in-depth write-up on how to solve this issue with measures. Link here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376/highlig...

Helper I

Hi Vicky,

thank you very much for the guidance but the link is not giving a definite answer either.

let me ask this way, is there a way to:

1- multiply product quantity *  number of orders = frequency

2- take weighted average of products (based on frequency)