cancel
Showing results for
Did you mean:
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. Customer Material Quantity Sales price per piece Total sales price Reference price per piece 1 Alpha A 4 10 40 10 2 Beta A 2 0 0 10 3 Gamma A 10 10 100 10 4 Gamma B 8 0 0 5

Desired output:

 Customer Total sales Free of charge Alpha 40 0 Beta 0 20 (2*10) Gamma 100 40 (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
Super User

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])``````

3 REPLIES 3
Super User

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

Frequent Visitor

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.

Super User

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])``````

Announcements

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors