Forum Discussion
Divide column not totalling
- 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/
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
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
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
- 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/