Forum Discussion
Help Please.. Custom Colum Filter Value Doesn't Match Measure Value
Hi again everyone!
I'm asked to measure and identify the customers who have purchased a specific product considering a dynamic range filter
On the screenshot below it shows that I have 2,740 customers who have access to purchase the said product
I adjusted the timeline to just Sept 27 purchases, thus the withTPSSale value shows 14 and without TPS is 2726, which is still correct
But the column filter I created so I can see who these customers are are (TPS Purchase Status v2) shows 244 -(fyi the 244 value seems to be the total of customers with a TPS purchase regardless of date)
This is the column forumla I created for TPS Purchase Status v2 ->
Again thank you for this kind community!
9 Replies
Heya,
Do you think you can provide a sample of your data (after you stripped it form any confidential information?
Either way, I would stay away from Blank values, especially when it comes to numerical data. Either in Power Query or in the table data itself replace the Blanks with 0 and see if the formulas are corrected.
Cheers,
PS: if this solved your problem then please mark it as the solution so others can see it.
Hi MNedix appreciate the input, I'll give it a try, though not quite sure how to replace the blank values to 0 as a sale can either be a credit or invoice and always has values, just that they show as blank on dates that a customer doesn't have a purchase..
unfortunately I am unable to send in the data as it has lots of conf info 😞
my first time doing such filters/dynamic conditions so not quite sure where I'm headed really haha, but I will try not to use blanks and see where I go from there thanks again
In Power Query (Transform Data), you can simply go to Transform -> Replace values
You can leave "Value to find" empty and just put 0 in "Replace with"
right the problem is that the sales values always have values in the raw data, they just go as blank on the report when the dates are already filtered in a way that some customers doesn't have any sales for that date range so replacing value from blank to 0 doesn't seem to be applicable on my raw data
Two other things:
1. Why do you go through an intermediate Column step for the status? The way I see it, you do:
- Step 1/ Column 1 : IF the sale is > 0 then "With TPS" else "Without TPS"
- Step 2/ Column 2: If Column 1 is "With TPS" then "With TPS" else "Without TPS"
Why don't you use just one column for the status (as below)?
Status = IF(NetSales_CBL > 0, "With TPS", "Without TPS")This may also get rid of the two errors you have in there.
2. Another way to get rid of the blanks (if you can't touch the imported data) is to create a new column as below:
NetSales_CBL_Forum = IF([NetSales_CBL] <> 0, [NetSales_CBL], 0)
Just replaced my measure to NetSales > 0, it still tags the right measure at 14 customers but the custom column still doesn't calculate right and tags some customers with TPS purchase even if they don't have any, the same customers who have overall TPS purchase regardless of date, so I think it's a problem with the date on how to include it on the calculation or measure not sure ... 😕
Hi mangchaaBI
Has your problem been resolved? If so, could you mark the corresponding reply as the solution so that others with similar issues can benefit from it?
Best Regards,Jayleny
