Comparing top selling products to products customers have on order

[gaiusgw]

I am not sure how to go about this and would love some feedback. I would like to be able to identify products customers are missing from top ranked products. 

Top selling products come from calculate(sum(OPENDET[ORIG_QTY]) and product name comes from OPENDET[STYLES] and looks like this. 

When no customer is selected on the page, the result of the measure above is the total for all customers. I would need to be able to select a customer and have it compare to the total as if no customer is selected. I would like the result to highlight what a selected custom does not have. 

I am sure more info might be needed so please let me know what details might be needed. Thanks. 

[mahoneypat]

If your customer column is in a separate table, you may be able to just add + 0 to your current measure.  Products they didn't buy should then show in your table with a 0.

Pat

[gaiusgw]

I gave this a try but the issue while it true that it prevents items with 0 units ordered from being filtered out when customer slicer is selected, it also changes the order of what does show and displays 0 units in alpha order so I lose my ranking based on volume of units. Maybe I am missing something? 

[v-eqin-msft]

Hi @gaiusgw ,

Sorry for that the information you have provided is not making the problem clear to me.🤔

Please tell us what you expect to help us clarify your scenario.

Best Regards,
Eyelyn Qin

[gaiusgw]

Here is another way to put it. I have a slicer for customer name. When no customer is selected, the styles fields shows total units for all customers. When i choose a customer on the slicer, the styles field is narrowed down to just what they have ordered. I need to be able to generate a result that indicates what the customer does not have on order from the full list. So basically, it would be a measure that compares the slice selected customer's styles and the full list when no customer slice is selected. I hope that clears it up.  

[Ashish_Mathur]

Hi,

You may drag my measure (see my previous reply) to the Styles table and then filter that measure (in the filter pane) by blanks.

[gaiusgw]

Hi Ashish,

I tried your measure but cannot get it to work. Here is a snip of it:

Units on my table is sum(OPENDET[ORIG_QTY]. I replaced units from your suggested measure with OPENDET[ORIG_QTY] and got the error above. If I add sum, it says there are too many arguments. I am not sure what I am missing to get your measure to work. 

Would love some more feedback. Thanks a lot. 

[Ashish_Mathur]

Hi,

The first input of the CALCULATE() function has to be a measure.  So it should either be [units] or SUM(OPENDET[ORIG_QTY]).  If it still does not work, then share the link from where i can download your PBI file clearly showing the error.

[Ashish_Mathur]

Hi,

This measure should work

All sales = calculate([units],all(OPENDET[STYLES]))