Classify inactive clients

[Anonymous]

Hello.

Here's what i got:

I have a base of clients which every month brigs me some revenue. 

I want to classify those clients into "active" and "inactive" based if they had any revenue on the current month.

For example:

Client | Revenue | Month | Status

Jonas      1.000       Jan        Active

Jonas           0        Feb        Inactive

Lucas    1.000        Jan         Active

Lucas    1.000        Feb         Active

In order to do so, i did the following measure:

if(revenue>=0,"active","inactive").

Which, as a table, worked great, showing active and inactive clients.

But when i try to count the total number of clients and classify this total by status (active or inactive) power bi doesn't allow me to use the "status" measure as subtitle(legend). 

Is there any suggestion on how can i fix this?

[daxer-almighty]

@Anonymous 

To create CORRECT models, please read this fist. If a store does not have an entry in a fact table, then in a correct model it's not an issue to get this store displayed in a visual and measures associated with it. Only in badly designed models this is an issue.

[Jihwan_Kim]

Hi, @Anonymous 

In my case, I usually create Grouping Table.

Inside here, I input Active and Inactive into one column. Then, I think the measures can be easily assigned to one of them.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

That's an interesting solution Jihwan. But i'm facing the following problem when going this way:

Let's suppose client Jonas didn't had any revenue on february month, so it has no value for revenue on that month.

When creating a grouping table, it will classify only the clients that have value for revenue on the specifc month (when filtering month). How about the ones that are inactive, and donesn't have any value?

Do you have any idea how to solve this?

[Jihwan_Kim]

Hi, @Anonymous 

Thank you very much for your feedback.

You are correct, and if there is no value for a certain client, then the graph or chart might show zero value and no name.

I was mixing the ideas with segmenting the clients into low value / medium value / high value kind of structure.

However, I think, how many times inactive clients are shown can be useful information. And the appearance count for inactive clients can be shown on the graph or chart.

I am not sure if this kind of information is needed in your business, but please kindly have a look at the link below, which is the sample pbix file.

Thank you very much.

https://www.dropbox.com/s/mrgyzyuakjb7zfz/pedroguimaraes.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[Anonymous]

Thanks Jihwan, your model was pretty insightful for me.

But i'm still facing one problem: whenever a store has no revenue on current month, my system doesn't export it with value 0 for revenue, so i have no record for that store on the current month.

Because of that, whenever i try the solutions we talked about, power bi only recognize stores that have value for revenue on the specif month.

I'm starting to think that my only solution will be adjusting my system to export value 0 for revenue for stores that didn't sell anything on that month.

Do you have any thoughts about that? any simple way?

[Jihwan_Kim]

Hi, @Anonymous 

Thank you very much for your feedback.

I am not sure about the detailed situation, but if I may say based on my previous experience only, 
even though the system only exports the data that has the revenue for the current month, I think you already have the dimension table (list of stores) somewhere.

In this case, you can already create a simple report for how many stores are zero for the current month, even the system does not export the store list for zero. 

I don't have detailed information, but I think no need to change the current process of your system, I think.