PowerBI Running Total error

[scd66]

I am trying to multiply 3 columns together in a PowerBI Table Visual and this seems to work well for the row values and gives the correct answer, but it seems to produce a huge running total which is much higher than the sum of all the values in the calculated column. I think I have an error in my formula but don't know how to fix it. I have shown a snapshot below showing the formula. The running total should be 567,082.24sqm, but it shows a huge number instead. I am multiplying an area (NIA Area sqm) column by two percentage columns (Percentage Org) and (Percent Usage Activity Tag). The percentage columns are in a different table to the area column. The formula gives the correct answer for each row, but not the total. Could you please help me enter the correct formula to fix the total?

[SamanthaPuaXY · Accepted solution]

Hi @scd66 ,
you are summing the individual columns then doing the product. Instead you could put all the columns in the same table, then use this formula:
Measure = SUMX(Table, Column1 * (Column2/100) * (Column3/100))

Hope this helps, do give a kudos if it solves your problem 🙂

[pankajnamekar25]

Hello ,

You can try below measure

NIA Area Used2 =
SUMX(
Locations,
Locations[NIA sqm] *
RELATED(Merge Org Occ Usage[Percentage Org]) *
RELATED(Merge Org Occ Usage[Percent Usage Activity Tag])
)

if it is not helping share sample pbix

[SamanthaPuaXY · Accepted solution]

Hi @scd66 ,
you are summing the individual columns then doing the product. Instead you could put all the columns in the same table, then use this formula:
Measure = SUMX(Table, Column1 * (Column2/100) * (Column3/100))

Hope this helps, do give a kudos if it solves your problem 🙂

[scd66 · Accepted solution]

Thank you so much Samantha. I got your formula to work when I merged the area table to the percentages table and it now gives the correct running total.