Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
amsrivastavaa
Helper III
Helper III

Filter Power BI data based on Selected filter range value

Hi Folks!!

I have table data as below 

 

ProjectTypeYearMeasure
P-1A2017100
P-1A201750
P-1A201710
P-1A201820
P-1A201855
P-1A201975
P-1A201985
P-1A201990

 

Filters are 

Range : holds four entry such as <25%; 25-50%; 50-75%; 75-100%   - Single value Select filter

Year : can have multiple year such as 2017,2018,2019                - Multi value select filter

Type : Hold all the value of Type - Single select value filter

 

I need to filter out data based on PERCENTAGE OF MEASURE out of sum of MEASURE of selected data.

 

To elaborate 

User has select the below combination of filter

TYPE="A";

YEAR=2017

RANGE =<25%;   

 

RANGE <=25% means is percentage of Measure against sum of measure in complete selected data; Measure/SUM(Measure); example elaborated below

 

Below, range has been calculated as below

 

TypeYearMeasureRange % 
A2017100(100/160)*100 = 62.5% 
A201750(50/160)*100 = 31.25% 
A201710(50/160)*100 = 6.25% 

 

Now, for above selected range, only third records with Measure 10 will qualify for the output as it is 6.25 i.e. less than 25%

i.e. for TYPE="A"; YEAR=2017 and RANGE =<25%;, only third row with Measure=10 will qualify for the output.

 

Similarly, If user SELECTED multiple year 2017 and 2018 from YEAR filter, then percentage need to calculated based on sum of measure for both 2017 and 2018 year, i.e; MEASURE/SUM of MEASURE OF YEAR 2017 + SUM of MEASURE of YEAR 2018.

 

Please suggest!!

 

Thanks

Amit 

11 REPLIES 11
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of your PBI file in which you have already written the measure.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Please share your email id as I am not getting any option to attach file here in Power BI Community page.

 

Also, I have official One drive where I am not allowed to share file with outside organization.

 

My Email Id : amit.260183@hotmail.com

 

Thanks

Amit Srivastava

Hi,

Upload your file to Google Drive and share the download link here.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

I have attached the file at OneDrive, please find the same

 

 

https://1drv.ms/u/s!Ahtm7otFIxr8avFXPCCDD0s64aU?e=QkTHKE

 

 

Please let me know in case you have any problem in accessing the same.

 

Thanks

Amit 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

Thanks for the reply.

I have gone through your PBI and below are my findings

 

Bucket Filter : When I have selected <25% in bucket filter, its not filtering table properly, even records those having more than 25% are qualifying for this filter which is not correct, as an example. In snapshot (which you have provided), Project =P-3 with amount =50 must not be part of the result set but it coming as a part of result set, please help.

 

My ultimate requirement is when user selects Bucket <25%, then table will have only those records where % of amount with total amount (Amount/Total Amount) is less than <25%, and once will have this dataset, then need to display summarized report via Matrix report (i.e. Matrix report will built on only data which is available after applying Bucket<25% filter), layout of report looks somewhat similar to the below (numbers shown are dummy records).

 

                     amsrivastavaa_0-1666414686711.png

 

Can understand, as logic you suggested is based on row to row basis so it will not work on summarized data, can you please help me how to do it in Matrix report.

 

Thanks

Amit

You are welcome.  50/220 = 22.72%.  Since this is <25%, this row should show up.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

HI Ashish, 

Yes, it will.

 

And how I would have below implementation on top of this data.

 

My ultimate requirement is when user selects Bucket <25%, then table will have only those records where % of amount with total amount (Amount/Total Amount) is less than <25%, and once will have this dataset, then need to display summarized report via Matrix report (i.e. Matrix report will built on only data which is available after applying Bucket<25% filter), layout of report looks somewhat similar to the below (numbers shown are dummy records).

 

                     

amsrivastavaa_0-1666419244613.png

 

 

Can understand, as logic you suggested is based on row to row basis so it will not work on summarized data, can you please help me how to do it in Matrix report.

 

Please suggest!!

 

Thanks

Amit

I am not sure of how to solve your matrix visualisation question.  Someone else will help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yangliu-msft
Community Support
Community Support

Hi  @amsrivastavaa ,

 

Here are the steps you can follow:

1. Create calculated table.

Year_Slicer =
DISTINCT('Table'[Year])

vyangliumsft_0-1666317028096.png

2. Enter data – Create table.

vyangliumsft_1-1666317028099.png

3. Create measure.

value =
var _selectyear=SELECTCOLUMNS('Year_Slicer',"Year",[Year])
var _sunmall=
SUMX(FILTER(ALL('Table'),
'Table'[Year] in _selectyear&&'Table'[Type]=MAX('Table'[Type])),[Measure])
return
DIVIDE(MAX('Table'[Measure]),_sunmall)
Flag =
var _selectrange=SELECTEDVALUE('Range_Slicer'[Range])
var _selectyear=SELECTCOLUMNS('Year_Slicer',"Year",[Year])
return
SWITCH(
TRUE(),
MAX('Range_Slicer'[Range])="<25%"&&[value]<0.25&&MAX('Table'[Year]) in _selectyear,1,
MAX('Range_Slicer'[Range])="25-50%"&&[value]>=0.25&&[value]<0.5&&MAX('Table'[Year]) in _selectyear,1,
MAX('Range_Slicer'[Range])="50-75%"&&[value]>=0.5&&[value]<0.75&&MAX('Table'[Year]) in _selectyear,1,
MAX('Range_Slicer'[Range])="75-100%"&&[value]>=0.75&&[value]<1&&MAX('Table'[Year]) in _selectyear,1,0)

4. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_2-1666317028100.png

5. Result:

vyangliumsft_3-1666317028106.png

If you need pbix, please click here.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi @v-yangliu-msft ,

Thanks for the response.

 

Here are few things which is required as a part of solution.

 

1. When I am not selecting any values in either YEAR and Range filter, nothing comes up as shown below, ideally it will suppose to display complete set of data , please suggest

amsrivastavaa_0-1666330418499.png

 

2. When I am selecting multiple Year say 2017 and 2018,VALUE need to be calculated based on VALUE/(sum of VALUE of that year), i.e. if there is value in 2017 year, it will be calculated as VALUE/[sum(VALUE) of 2017], and for YEAR 2018, it must be VALUE/SUM(VALUE) of 2018.

 

However, it works well when I am selecting only ONE YEAR say 2017 in Year Filter, but it doesn't works well as soon as i will select another year say 2018 alongwith 2017, it shows incorrect values, as shown below.

 

amsrivastavaa_1-1666330880516.png

 

i.e. In first snapshot-1, where only year selected is 2017, it shows value as 6.25% which is correct however in snapshot-2, I have selected 2017 and 2018. value shown as 4.25% which is incorrect, it suppose to 6.25$ only, please suggest!!

 

Please assist and suggest!!

 

Thanks

Amit 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.