Help Please.. Custom Colum Filter Value Doesn't Match Measure Value

[mangchaaBI]

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 -> 

TPS Purchase Status v2 = IF([mTPS Purchase Status] = "With TPS Purchase","With TPS Purchase","Without TPS Purchase")

This is the measure I created to tag a customer if With or Without Purchase --> 

mTPS Purchase Status = IF([NetSales_TPS] <> BLANK(),"With TPS Purchase","Without TPS Purchase")

Where NetSales_TPS is the based on the Sales TPS product 

What I would want to happen is that the Column TPS Purchase Status v2, would also count 14 instead of 244

Not sure what I'm missing, because the mTPS Purchase Status only shows 14 but the Column doesnt 😞 

Again thank you for this kind community! 

[v-jialongy-msft]

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

[mangchaaBI]

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 ... 😕

[MNedix]

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"

[mangchaaBI]

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

[MNedix]

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)

[mangchaaBI]

Hi @MNedix 

Really appreciate your help! think we're close and just confirmed my thought that the issue can be with the date but not sure how to work it out... 

1. I did this one previously that's why the one I sent has a v2 because this one has the same result (see snapshot below) 

2. now #2 recommendation, I think it still flagged the customers as with Purchase (shows netsales value) but the problem there is that their NetSales were done not within the filtered date range of Sept 27. If you notice that the first 14 had NetSales_CBL values and their equivalent NetSales_CBL_Forum values, but you have another 2 showing (not a complete list) and they have values in NetSales_CBL_Forum (column) but they don't have a value in the NetSales_CBL (measure)

If it would further help, the Customer list that I placed in there is from a table that's joined to the sales table, where as the sales table has dates of sales, itemname, sales value etc.. 

[MNedix]

I think the issue is deeper into the data model, you should not have the two circled values in there and the empty value in between them is also an error which should not appear. It's quite difficult to understand where this is coming from without looking at the model.

What is the NetSales_CBL measure?

[MNedix]

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.

[mangchaaBI]

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