Hi,
I’m working on a dump/damaged report. It’s by item with multiple amount columns. One for sales for the item, and one for the amount dumped or damaged. That part is easy. The tricky part is I only want to show items that have a dump on them. So basically filter the dumped column to exlude all zero amounts.
But I can’t figure out how to do that. Any ideas?
Thanks,
I’m struggling to understand exactly what’s required here.
Need some images to review so I can get a better idea of the overall scenario.
You can also add the file also if you can do that.
Not sure what is meant by dump/damaged report?
As mentioned as many images showcasing the scenario is the best way for me to assist quickly on this.
Chrs
We sell milk products so dumped refers to items we had to take back because (for example if they are expired).
I did a quick example in excel. Basically I would want to filter the report so it excluded the items in red (ie items that had sales but nothing was dumped).
Thanks,
Got it.
Ok this should be super easy.
You just need to add a FILTER to you calculation.
Something like this.
CALCULATE( SUM( Qty Sold ) ,
FILTER( Table, Qty Dump > 0 ) )
This should be all you need to solve as per how you described.
See how you go with this.
When I do that I I get zero results. Basically it only is summing up each invoice that has a dump amount on it. When what I want is the total amount of sales of that item number if any of that item number has a dump.
Programically it would be something like
If ITEM has dump qty > 0 then sum sales for that item
Do I need to do something with an ALL statement?
Then for that is should be a simple IF statement that should get you the results I would say.
IF( Qty Dump = 0, BLANK(), SUM( Qty Sold) )
Any reason why this wouldn’t get you what you need?
What is the context of the calculation in the visual you are looking to show this in?
That did it, thanks so much
Ok that’s great.
Hi,
slight change of specs. I’ve now got the data being spread accross columns as weeks. So I need it to show all the weeks sales if the total for all the weeks is greater than zero.
Just so I fully understand this, you’re just trying to show every week in the table? Is that right?
If so then you need to wrap another IF statement around the formula that has logic like this.
IF( ISBLANK( Current Measures), 0, Current Measure )
The zero will make sure you have a result in every row.
Another way to do this is by use the below
You can access this by the little dropdown here from your table
Sorry let me rephrase it.
We have the formula IF( Qty Dump = 0, BLANK(), SUM( Qty Sold) ) which is worked fine when there was no date/time context.
However, now I’ve added a WEEK context so what that is doing is only showing the sales data for each week IF there is dump for that week. When really what I want to do is show the sales for all the weeks for that item if there is dump on any of the weeks for that item.
So something like
IF( Qty Dump TOTAL = 0, BLANK(), SUM( Qty Sold) )
I think maybe I can use isfiltered, but it’s not working quite right. I’m just getting the total
Here is the data with no dump filter
Gross Qty Sold no filter = -CALCULATE(SUM('Item Value New'[Invoiced Quantity]),FILTER('Item Value New','Item Value New'[Reason] = "Sales"))
Here I’m just getting the total sales
Gross Qty Sold (w) =
var dumpTotal = if(ISFILTERED(Dates[WeekOfYear])=FALSE(),[Qty Dump],0)
Return
-IF(dumpTotal = 0, BLANK(),CALCULATE(SUM('Item Value New'[Invoiced Quantity]),FILTER('Item Value New','Item Value New'[Reason] = "Sales")))
to clarify you can see on item 01678 you only see the total sales of 15 units, not the 5,6,4 sales per week you see in the first pic
Ok, I think I got it figured out. I used ALL to take to calculate a dump total.
Gross Qty Sold =
var dumptotal = CALCULATE(SUM('Item Value New'[Invoiced Quantity]), ALL(Dates[WeekOfYear]), ('Item Value New'[Reason] = "dump" || 'Item Value New'[Reason] = "damaged"))
return
-IF(dumptotal =0 ,BLANK(), CALCULATE(SUM('Item Value New'[Invoiced Quantity]),FILTER('Item Value New','Item Value New'[Reason] = "Sales")))Ok great.
I was reading through this and am at the point where I would need to see the model to understand all the variable at play on this one.
If it’s working that great.