simple multiply columns having empty lines . but BI multiplies sums also

[pauliuseg]

hi,

i have simplified example, just : Sales and discounts. but some discount lines are not existing. so multiplying from zero should be zero.

tried to add  column if discount is missing return 0.

so 3 formulas, all calculate rows OK  2126,02 ant 67,75, and zero. But Total is 3966,39  , and should be 2193,77, not 3966,39 (as it total sales multiplies by sum discount which is in total 0.5... )

3 formulas tried

Disc Sum = SUM('DB_Sales'[Sales]) * SUM('Disc_table'[Discount])

Sales x Disc = SUM('DB_Sales'[Sales]) * [Discount (if null then 0)] - then it returns zero 🙂

sales x Disc sumx = SUMX('DB_Sales','DB_Sales'[Sales]) * [Discount (if null then 0)]  - this with sumx should work .. but ...

what i miss ? should be simple but it's not.... 

thanks a lot ! 

[parry2k]

@pauliuseg did you read my previous message, if you need help, you need to provide the details.

[parry2k]

@pauliuseg it is important to provide a full picture of how your model is done. I don't why you would use SUM, Share how your model looks like and how these tables are connected. 

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[pauliuseg · Accepted solution]

hi,

finally found, i need to use related 🙂

SUMX('DB_Sales','DB_Sales'[Sales] * RELATED (Disc_table[Discount]) )

and works perfectly :), also i have added additionally a simple key between these tables instead of using more realtions.

[pauliuseg]

i used sum, as it says that i'm refering to column with many values.  even in discount table i left 3 unique records.

i can use max instead of sum, still result is the same - by line it's ok, but total result is wrong.

i know, now i cant  provide, as file too big need to do seperate load, or just multiply in Databse. but did not expected to be it so hard 😄

[parry2k]

@pauliuseg try this

sales x Disc sumx = SUMX('DB_Sales','DB_Sales'[Sales] * 'DB_Sales'[Discount] )

or

sales x Disc sumx = SUMX('DB_Sales','DB_Sales'[Sales] * COALESCE('DB_Sales'[Discount],0) )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[pauliuseg]

hi,

sad, but no, as discount table is different, i need to use some like sum, max or smth.

used sum, even i know thats single value for hat month

sales x Disc sumx n1 = SUMX('DB_Sales','DB_Sales'[Sales] * COALESCE(SUM(Disc_table[Discount]),0) )

sales x Disc sumx n2 = SUMX('DB_Sales','DB_Sales'[Sales] * SUM('Disc_table'[Discount]) )

and the result is the same

i'm already thinking to do left join in database, and then it will be easy, as there i see some problem is that i'm multiplying with discount table. as everything i multiplied before with sumx was in same table ...

but all works looking at single line 😄