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