Forum Discussion

NeimadB's avatar
NeimadB
Helper II
4 years ago
Solved

Create a « Not In » Gauge

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 

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

7 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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.

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    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's avatar
      NeimadB
      Helper II
      4 years ago

      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's avatar
        v-jingzhang
        Community Support
        4 years ago

        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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.