Forum Discussion

amsrivastavaa's avatar
amsrivastavaa
Helper III
3 years ago

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 

Need Help

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  amsrivastavaa ,

     

    Here are the steps you can follow：

    1. Create calculated table.

    Year_Slicer =
DISTINCT('Table'[Year])

    2. Enter data – Create table.

    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.

    5. Result:

    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

    • amsrivastavaa's avatar
      amsrivastavaa
      Helper III
      3 years ago

      Hi Anonymous ,

      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

       

      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.

       

       

      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 

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

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

    • amsrivastavaa's avatar
      amsrivastavaa
      Helper III
      3 years ago

      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 : [email protected]

       

      Thanks

      Amit Srivastava

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

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