Forum Discussion
Create a « Not In » Gauge
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.
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
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 ?
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.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 ?
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.