Category missing from Sales table summarized by Group


#1

Hi Sam,

I’ve been watching your video “Dynamically Group Customers by Ranking”. Following the indications, I created the measure to group the customer categories I created the other day (thanks to another great video of yours) based on their sales. While I was checking the results, I realized that the sporadic category (lost I assume it should be blank) is missing from the table … any thoughts about why this could have happened?

This is the detail about what I’m explainig…

Thanks as always for your support

Lucia


#2

Really the only reason this would be is that there is no results under sporadic.

When there is no values Power BI will just insert BLANK and when the results is blank a table won’t show it.

You can show this though if you need to.

It the inputs sections go to this drop down

image

Then turn this on

image

See how you go with this.

Sam


#3

That’s not the problem. I’ve been reviewing all the formulas and results and I found out that many fields in my Last Purchase Date are blank when those customers have purchased in the period selected.

I used the following formula for the measure and it worked for some but not for all the customers :frowning:

Last Purchase Date = 
CALCULATE( MAX( 'SALES '[SALES DATE]),
      FILTER( ALL( 'SALES '[SALES DATE] ), 'SALES '[SALES DATE] < MAX( 'SALES '[SALES DATE] ) ) )

This is the result that is showing

Any ideas about what I’m missing?

Thanks

Lucia


#4

It seems like some customers don’t have purchases registered but they are in the sales table…

although some others purchased but still the field in the Last purchased date is blank


#5

Possible to attach the model here.

There’s just a lot at play here and it’s difficult to visualize everything without actually see the data.

Thanks


#6

Hi Sam,

I sent you by email the link with the access to the model. As you will see there are blank dates for Last Purchase Date, therefore, the sporadic category is missing from the table.

I hope you can find my mistake :slight_smile:

Thanks in advance for your help!

Lucia