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.

[keinshah]

I want the total replenishment cost to show the sum of indivudual costs and not as a product of total unit cost and total re -order quantity. how do I solve it?

[Ashish_Mathur]

Hi,

This pattern should work

Measure = SUMX(VALUES(DATA[Item No.]),[Replishment cost])

[keinshah]

In the same table, I want to calculate replishment cost only if Purchase = YES. What would be the best way to do it?

[Ashish_Mathur]

Share the download link of the file and show the expected result clearly.

[keinshah]

Replenishement cost = reorder quantity * unit cost. But only calculate if Purchase = YES. Blank if purchase = NO and show the sum of the total replenishment cost at the bottom. 

[keinshah]

thanks a lot. that worked. 

[Ashish_Mathur]

You are welcome.

[keinshah]

I managed to get the calculation. however, the sum is not showing correct. any ideaas?

[surajlodh_maha]

I've having this same issue.  Is there any way the formula can be shared since the PBIX file is no longer available?

[Ashish_Mathur]

Hi,

I do not have that file.  Share some data, explain the question and show the expected result.

[surajlodh_maha]

I've having this same issue.  Is there any way the formula can be shared since the PBIX file is no longer available?

[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!

[rmooney]

I've having this same issue.  Is there any way the formula can be shared since the PBIX file is no longer available?

[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