How not to apply cost formula in the Total

[alexglickfire]

Hello!

Currently building a report for overstock and the formula that was made by DAX is also applying to the total column therefore show a huge number that is entirly wrong, how do i fix this?

I simply am just trying to get the column total of total cost but am stumped at this point.

any help is appreciated

current formula:

PartCost x QtyOnHand =

SUM('Parts'[PartCost]) * SUM('qryForPartsQtyData'[QtyOnHand])

[audreygerred]

Hello! You are going to want to use an interator function to handle this. I created some mock data to explain:

Here, I have the Total Cost showing as on-hand * cost at the row level in the table, but the total shows a sum of those. For this, you need SUMX in your measure.

Total Cost = SUMX('YourTable','YouTable'[On-Hand]*'YourTable'[Part Cost])

This way, the total is the sum of each on-hand* cost rather than the sum of on-hand * the sum of cost.

[alexglickfire]

Fairly new sorry if i seem real confused but here is what i am getting

bigger look of the table/table view

[alexglickfire]

not sure why it did not pull but i have gotten this far

[audreygerred · Accepted solution]

Woops - sorry, I missed that you had two separate tables. You can try 

TotalValue = 
SUMX(
    OnHandTable,
    OnHandTable[on-hand] * RELATED(PartCostTable[part cost])
)

[alexglickfire]

Thank You!

[audreygerred]

You're very welcome!

[DataNinja777]

Hi @alexglickfire ,

May I ask what the name of the field is that you are placing in the rows of your matrix table? You can try to get the total to add up by using the SUMX function over that column in the rows of the matrix table. Alternatively, you can skip the total row by applying the IF and HASONEVALUE formula

PartCost x QtyOnHand (without total) = IF(HASONEVALUE('Your table'[Column name on row]), [PartCost x QtyOnHand], BLANK())

 Best regards,