cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RonSon2015
Frequent Visitor

SUMX filter (value smaller 1) does not find correct results

Hello,

After trying many different formul and search terms I cannot find a solution for the following problem.

I've got a fact table with sales by customer and product. However, it could happen that products are given for free. In this case it should calculate the missed sales (free of charge) by multiplying the quantity with the reference price.

Fact table:

Invoice No.CustomerMaterial   QuantitySales price per pieceTotal sales priceReference price per piece
1AlphaA4104010
2BetaA20010
3GammaA101010010
4GammaB8005

 

Desired output:

CustomerTotal salesFree of charge
Alpha400 
Beta020(2*10)
Gamma10040(8*5)

 

My idea which is not working (result on lowest level as well as on aggregated level is wrong):

 

Missed sales = SUMX(

FILTER ( Fact_Table; Sales price per piece < 1) ;

Quantity * Reference price per piece

)

 

(smaller 1 because some products are not given at zero but for some cents)

 

 

Thanks for any hints and suggestions in advance!

1 ACCEPTED SOLUTION

Here's a modified measure

 

free of charge 2 = 
var c=SUMMARIZE('Table','Table'[Customer],"free",sumx('Table',if('Table'[Sales price per piece]=0,'Table'[Quantity]*'Table'[Reference price per piece],0)))
return sumx(c,[free])

 

lbendlin_0-1632423776344.png

 

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

see attached.  You didn't mention if you wanted working totals.

Thank you for your effort to create that example in PowerBI. It's a solution but I also need working totals to answer the question: What is the total value of freebies and then to analyze to what customer and what products were given for free.

Here's a modified measure

 

free of charge 2 = 
var c=SUMMARIZE('Table','Table'[Customer],"free",sumx('Table',if('Table'[Sales price per piece]=0,'Table'[Quantity]*'Table'[Reference price per piece],0)))
return sumx(c,[free])

 

lbendlin_0-1632423776344.png

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.