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...
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.

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.

      • NeimadB's avatar
        NeimadB
        Helper II
        4 years ago

        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 ?