Need to write a measure or custom column which will filter table data and show the count for same

[ukantkumar123]

Date	City Number	Planned %	Actual %
21-09-2023 00:00:00	300656	1.19	0.69
28-09-2023 00:00:00	300656	1.19	0.69
05-10-2023 00:00:00	300656	0.81	0.7
21-09-2023 00:00:00	300671	0.03	0.87
28-09-2023 00:00:00	300671	0.02	1.34
05-10-2023 00:00:00	300671	0.03	1.87
12-10-2023 00:00:00	300671	0.03	0.88
19-10-2023 00:00:00	300671	0.03	0.78
26-10-2023 00:00:00	300671	0.03	1.06
02-11-2023 00:00:00	300671	0.03	0.98
26-10-2023 00:00:00	300672	1.02	0.05
02-11-2023 00:00:00	300672	0.45	3.33
21-09-2023 00:00:00	300684	3.37	2.82
28-09-2023 00:00:00	300684	3.03	1.39
05-10-2023 00:00:00	300684	3.26	1.59
12-10-2023 00:00:00	300684	2.75	1.39
19-10-2023 00:00:00	300684	2.81	1.48
26-10-2023 00:00:00	300684	2.65	1.73
02-11-2023 00:00:00	300684	2.73	1.62
21-09-2023 00:00:00	300695	0.04	0.77
28-09-2023 00:00:00	300695	0.03	0.35
05-10-2023 00:00:00	300695	0.04	0.49
12-10-2023 00:00:00	300695	0.04	0.32
19-10-2023 00:00:00	300695	0.04	0.37
26-10-2023 00:00:00	300695	0.04	0.41
02-11-2023 00:00:00	300695	0.04	0.36
26-10-2023 00:00:00	300696	0.59	0
02-11-2023 00:00:00	300696	0.19	3.3
05-10-2023 00:00:00	300711	0.04	0.17
12-10-2023 00:00:00	300711	0.13	0.17
19-10-2023 00:00:00	300711	0.23	0.28
26-10-2023 00:00:00	300711	0.22	0.38
02-11-2023 00:00:00	300711	0.43	0.5
19-10-2023 00:00:00	300713	0.91	0.71
26-10-2023 00:00:00	300713	1.44	0.69
02-11-2023 00:00:00	300713	0.83	0.76
21-09-2023 00:00:00	300720	2.39	0.66
28-09-2023 00:00:00	300720	2.66	1.45
05-10-2023 00:00:00	300720	5.43	0.29
12-10-2023 00:00:00	300720	3.39	0.57
19-10-2023 00:00:00	300720	3.69	0.53
26-10-2023 00:00:00	300720	4.05	0.55
02-11-2023 00:00:00	300720	5.83	0.91
21-09-2023 00:00:00	300724	0.84	0.32
28-09-2023 00:00:00	300724	0.89	0.45
05-10-2023 00:00:00	300724	1.94	0.98
12-10-2023 00:00:00	300724	2.39	1.19
19-10-2023 00:00:00	300724	3.16	1.52
26-10-2023 00:00:00	300724	2.79	1.58
02-11-2023 00:00:00	300724	1.27	2.26
21-09-2023 00:00:00	300731	2.32	2.41
28-09-2023 00:00:00	300731	2.02	4.11
05-10-2023 00:00:00	300731	2.23	3.22
12-10-2023 00:00:00	300731	1.87	2.95
19-10-2023 00:00:00	300731	1.44	3.46
26-10-2023 00:00:00	300731	2.02	2.67
02-11-2023 00:00:00	300731	2.04	3.44

Planned % cumm = CALCULATE(SUM('Variance Test  Data'[Planned %]),FILTER(ALLEXCEPT('Variance Test  Data','Variance Test  Data'[City Number]),'Variance Test  Data'[Date]<=MAX('Variance Test  Data'[Date])))

Actaul % cumm = CALCULATE(SUM('Variance Test  Data'[Actual %]),FILTER(ALLEXCEPT('Variance Test  Data','Variance Test  Data'[City Number]),'Variance Test  Data'[Date]<=MAX('Variance Test  Data'[Date])))

Var% = 'Variance Test  Data'[Actaul % cumm]-'Variance Test  Data'[Planned % cumm]

Need one card Value which show count of Variance(Var%) lie inside the range of -5% to -25%.Also i need one button for same which will filter table data (Based on max date selection and "Var%" lie in to -5% to -25% )

[ukantkumar123]

@lbendlin Now I need one slicer or button if click on it it shord show filter all the data in table and show only same which is satisfying the condition in table

[lbendlin]

You can add that slicer yourself. What column should it be on?

[ukantkumar123]

@lbendlin @Ashish_Mathur 

Reg :My End Goal is to Fiter the metadata in table using Slicer.

Challenge:

My end goal is to dynamically filter metadata in a table based on specific conditions, and we have created a measure for this purpose. However, I am looking for an effective way to enable users to interact with this measure and filter the data accordingly.

Request for Guidance: I would greatly appreciate your insights on the best approach to achieve this. Are there alternative methods or best practices you could recommend for allowing users to filter metadata in a table using measures in Power BI?

I value your expertise and would be grateful for any guidance you can provide to help me overcome this challenge. If you have some time, could we schedule a brief meeting to discuss this in more detail?

Thank you in advance for your assistance, and I look forward to hearing from you.

[lbendlin]

- enable "Personalize visuals"

- enable "Allow users to change filters"

Train your users.

[ukantkumar123]

@lbendlin Thanks it's correct, now i need one slicer for same.

[ukantkumar123]

@amitchandak @lbendlin 

[lbendlin]

Your sample data is not fully covering all scenarios.

Do you need a single card, or a card by city?

[ukantkumar123]

Thanks for reply @lbendlin 

I need total count on card along with slicer (Slicer shoud filter table data)

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[lbendlin · Accepted solution]