Create a « Not In » Gauge

[NeimadB]

Hello there,

I'm wondering which type of a charts or measures  I can use to create a "Not In" Gauge.

i have a bunch of restaurants, a list of clients and I want to determine which clients have never ate at any restaurants.

I got a client table with a clientID and OrganisaitionID and FiscalYearID

and I have a Purchases table with clientID and FiscalYearID

My idea is that for a given OrganisationID and a given FiscalYearID, I want to count to the total clients for this organization is the Client Table, and then count the number ClientID that are not in the Purchases for the given FiscalYearID.

all this data to create a gauge.

im a bit lost on what to use.

thanks a lot 

Stan 

[Ashish_Mathur]

Hi,

I am not sure if i can create a gauge chart for you but i can help you with writing a measure.  Share some data and show the expected result.

[v-jingzhang]

Hi @NeimadB 

Can you provide some sample data about both tables and expected output? We can work on that to give a more detailed suggestion or solution then.

To display which clients have never ate at any restaurants, use Table or Matrix visual would be better to show client names or IDs. To count the numbers of "In" and "Not In", we need to create measures for that. 

Best Regards,
Community Support Team _ Jing

[NeimadB]

Hello,

actually the model is quite simple.

I got my clients table as followed 

report.ClientProfile

ClientId

OrganisationId

FiscalYearId

And my purchase table as followed

dimension.Purchases

ClientId

FiscalYearId

The compute is quite simple I guess :

I take all the clients of one or mutiple FiscalYear / Organisation, and then I check if their Ids are in the Purchase Table of the system for this given FiscalYear.

If they are here, I count them as "have purchased something in the system for the given period of time".

If not, I count the opposite.

And I would show this as a gauge, with number of customers for this FiscalYears/ Organisations as maximum value, and how many Customer have purchased something in the system VS the total count.

Purchased are fact table improted "as is" from my Datawarehouse

ClientProfile Is a table that combine ClientId, FiscalYearId and OrganisationId (based respectively on dimension.Clients, dimension.FiscalYears and dimension.Organisations)

Do you need more input ?

[v-jingzhang · Accepted solution]

Hi @NeimadB

I mocked some data and created a sample on that. I used below measures. Download the attachment for details and adjust the measures to fit your data. 

Total Clients = DISTINCTCOUNT('report ClientProfile'[ClientId])

In Clients Count = 
VAR vYears = ALLSELECTED('report ClientProfile'[FiscalYearId])
VAR vClients = VALUES('report ClientProfile'[ClientId])
VAR vTable = FILTER('dimension Purchases','dimension Purchases'[FiscalYearId] IN vYears && 'dimension Purchases'[ClientId] IN vClients)
RETURN
CALCULATE(DISTINCTCOUNT('dimension Purchases'[ClientId]),vTable)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[NeimadB]

I think you are right, your solution is fine (but I had to create a subview of the fact.Purchases table with only Year and CustomerId, becase I had a blank value).

To conclude, I also want to show the percentage in addition to the gauge, any suggestion how to achieve that ?

[v-jingzhang]

Hi @NeimadB 

If you want to create a subview of the fact.Purchases table, you can add a table visual into the report page and drag Year and CustomerId columns into it as Values field. 

For the percentage, as you already have [Total Clients] and [In Clients Count] measures (in my previous reply), you can create one more measure Percentage = DIVIDE([In Clients Count], [Total Clients]). Under Measure tools tab, modify this measure into the percentage format. Then add a card visual and put the measure in it. 

Reference:

Table visualizations in Power BI reports and dashboards - Power BI | Microsoft Docs

Card visualizations (big number tiles) - Power BI | Microsoft Docs

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[lbendlin]

Use the clientid field from the client table, add a measure "Restaurants visited" and then enable "select items without data" - if you then filter the "Restaurants visited" to Blank  you get all the clients who did not visit any restaurants.