multiply column and a measure - wrong total

[timurcakin]

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.

thanks in advance

best regards

tim

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[timurcakin]

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

thanks in advance

timur

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[timurcakin]

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!

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark that reply as Answer.  Try this measure

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

Hope this helps.

[timurcakin]

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?

[Ashish_Mathur]

You may download my PBI file from here.

Hope this helps.

[timurcakin]

Absolute Legend!!

thank you very much Ashish

[Ashish_Mathur]

You are welcome.

[timurcakin]

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?

[vicky_]

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

[timurcakin]

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)

appreciate your feedback