CALCULATE unit price*Qty, Filter Disc = 100

[Anonymous]

Guys,

I'm really desperate!!

Can't seem to find the right solution for this problem. I would be very much apreciated if any of you can help me.

My table has sales records with and without discount percentage. I want to multiply unit by quantity if disc equals to 100
Something like this:

GrossOffered = CALCULATE(SUM('Table'[Un Price]) * SUM('Table'[Qty]),

FILTER('Table','Table'[Disc]=100))
This measure works (i think) but the real problem is when it sums. The totals for this measure are wrong. Also, this visual has a filter to show me only those records with Disc = 100
Thank you so much all of you

Best regards

Pedro

[SteveCampbell · Accepted solution]

The measure you wrote will sum the entire of each column, then multiply.

What you really want, is to go row by row and multiply, then sum the result.

This is filter vs row context. You need a row iterator, which is SUMX.

A	B
1	1
2	2
3	3

For the above  example,

Filter Context = Sum(A) * Sum(B) = (1+2+3) * (1+2+3) = 6 * 6 = 36 

Row Context = SUMX (table, A * B) = (1*1) + (2*2) + (3*3) = 1 + 4 + 6 =15

So, your code should be:

GrossOffered = 
              SUMX(FILTER('Table','Table'[Disc]=100),
              'Table'[Un Price] * Table'[Qty]   )

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  
Read my blogs on  

[Anonymous]

Hey Steve, somehow when the qty is bigger than 1 it doesn't multiply correctly....but the totals and subtotals are correct. Do you know how to fix this?

[Anonymous]

Hey Steve

Thank you so much. It works perfectly!!!! Awesome!!!

Best regards

Pedro

[amitchandak]

You should create a new column and multiply unit price and Qty. Sum(price)*sum(Qty)  does not seem right to me

There use IF

IF(disc=100,unitPrice*qty)