Divide column not totalling

[katrina890]

Hiya

As you can see below, my PO Spend column is not totalling - it is doing the divide calculation along the totals.

This is the calculation that PO Spend is doing - PO Spend = [PO unit * QTY]/[Part Conv Total]

PO unit * QTY = SUMX('Purchase Order Receipts',[QTY In Store] * 'Purchase Order Receipts'[BUY_UNIT_PRICE])

Part Conv Total = SUMX('Purchase Order Receipts',[Supplier for Purchase Part.PRICE_CONV_FACTOR] * 'Purchase Order Receipts'[Supplier for Purchase Part.CONV_FACTOR])

Would someone be able to tell me what i need to change so that this column will total, like the others?

Thanks

Katrina 

[v-danhe-msft]

Hi @katrina890,

Based on my test, you can refer to below steps:

1.Due to I could not figure out your data of [Supplier for Purchase Part.PRICE_CONV_FACTOR] and [BUY_UNIT_PRICE] column, so I just create two measures and sample data.

Measures:

Part Conv Total = CALCULATE(SUM('Purchase Order Receipts'[Conv Total]))

PO unit * QTY = CALCULATE(SUM('Purchase Order Receipts'[PO unit * QTY.1]))

2.Create the divide measure.

Measure = SUMX(VALUES('Purchase Order Receipts'[QTY In Store]),[PO unit * QTY]/[Part Conv Total])

3.Create a Table visual and add related field, you could see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/ny6888hk2zlke08/Divide%20column%20not%20totalling.pbix?dl=0

Regards,

Daniel He

[katrina890]

I have tried the instructions given - I dont think this is quite what i am looking for.

After looking at my data - i dont want to sum 'Purchase Order Receipts'[Conv Total] field.

I have PO unit * QTY = SUMX('Purchase Order Receipts',[BUY_UNIT_PRICE] * 'Purchase Order Receipts'[QTY In Store])

This works.

I really just want to take PO unit * QTY and divide by PO unit * QTY = SUMX('Purchase Order Receipts',[BUY_UNIT_PRICE] * 'Purchase Order Receipts'[QTY In Store]) with the column totalling correctly.

Thanks

[v-danhe-msft]

Hi @katrina890,

I do understand what you want, but I could not reproduce your problem due to I could not get your data of [Supplier for Purchase Part.PRICE_CONV_FACTOR] and [BUY_UNIT_PRICE] column. And the [Part Conv Total] and [PO unit * QTY] are all measures right? So I have to create the two measures to test your problem. If you want me to reproduce your problem, could you please offer me your data of [Supplier for Purchase Part.PRICE_CONV_FACTOR] and [BUY_UNIT_PRICE] column or share the pbix file if possible?

Regards,

Daniel He

[katrina890]

Above is a screen grab that includes all the data I am using.

If this is not enough, let me know the best way to attach the pbix file.

Thanks

[v-danhe-msft]

Hi @katrina890,

Based on my test, you could modify your measure of [PO Spend] = [PO unit * QTY]/[Part Conv Total] to [PO Spend] =SUMX(VALUES('Purchase Order Receipts'[BUY_UNIT_PRICE]), [PO unit * QTY]/[Part Conv Total]). It could work.

Also you can test with our sample report on your side to see if the same issue occurs.

https://www.dropbox.com/s/llu28zzeg7e89ef/Divide%20column%20not%20totalling2.pbix?dl=0

Regards,

Daniel He

[GilbertQ · Accepted solution]

Hi there

Please refer to this blog post below.

What essentially is happening is that your measure is working on the rows, but when it gets to the Total Column there is nothing for the column to filter by. So when that happens it displays the incorrect result for your column. This only happens when you use a table or matrix.

In order to resolve this, you need to identify the total row and when you do apply a slightly different measure.

This blog post does a great job explaining that.

https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/