Data Range in Power BI

[amsrivastavaa]

Hi Guys!!

I landed into very weird kind of requirement, the same has been detailed below.

Source Data : 

Project	Type	Year	Channel	Amount
P-1	Credit	2017	1	20
P-2	Credit	2017	1	30
P-3	Credit	2017	0	200
P-4	Credit	2017	0	300
P-1	Credit	2018	1	20
P-2	Credit	2018	1	30
P-3	Debit	2018	1	10
P-1	Debit	2017	1	20
P-2	Debit	2017	1	30
P-3	Debit	2017	0	40
P-4	Debit	2017	0	50
P-1	Debit	2018	1	60
P-2	Debit	2018	1	70
P-3	Debit	2018	1	80

Filters :

1. Channel 

2. Type

3. Year

So far, I have created my Power BI report with above mentioned filters (Channel,Type and Year) with below Matrix as output which showing summation of Amount based on Type and Year. 

Type	2017	2018
Credit	550	60
Debit	140	210

Now, My further requirement is :

1- To implement Range filter that holds values like (<25%; 25-50%; 50-75%; 75-100%).

Lets say, User has selected Type=Credit and Range<25%, only records where Percenatge is <25% for Type=Credit will be considered as output

Project	Type	Year	Channel	Amount	Percentage
P-1	Credit	2017	1	20	3.64
P-2	Credit	2017	1	30	5.45
P-3	Credit	2017	0	200	36.36
P-4	Credit	2017	0	300	54.55
P-1	Credit	2018	1	20	33.33
P-2	Credit	2018	1	30	50.00
P-3	Credit	2018	1	10	16.67
P-1	Debit	2017	1	20	14.29
P-2	Debit	2017	1	30	21.43
P-3	Debit	2017	0	40	28.57
P-4	Debit	2017	0	50	35.71
P-1	Debit	2018	1	60	28.57
P-2	Debit	2018	1	70	33.33
P-3	Debit	2018	1	80	38.10

Data will be available after Type=Credit and Range<25%, will be as shown below

Project	Type	Year	Channel	Amount	Percentage
P-1	Credit	2017	1	20	3.64
P-2	Credit	2017	1	30	5.45
P-3	Credit	2018	1	10	16.67

Now, next step would be generate the data for all the Type and available year for these three Projects (for which above data is available after filteration) and then create Matrix based on that data.

I.e. need to have data for all the projects (P-1, P-2 and P-3) for Year (2017 and 2018) for all the Type, as shown below

Project	Type	Year	Channel	Amount
P-1	Credit	2017	1	20
P-2	Credit	2017	1	30
P-3	Credit	2017	0	200
P-1	Credit	2018	1	20
P-2	Credit	2018	1	30
P-3	Credit	2018	1	10
P-1	Debit	2017	1	20
P-2	Debit	2017	1	30
P-3	Debit	2017	0	40
P-1	Debit	2018	1	60
P-2	Debit	2018	1	70
P-3	Debit	2018	1	80

Then Matrix will be created as shown below 

Type	2017	2018
Credit	250	60
Debit	90	210

Note : If there is another filter say Channel=1, then Range will be applied on the data left after filteration of Channel=1 and then rest of the procedure will remain same.

Please suggest!!

Thanks

Amit 

[amitchandak]

@amsrivastavaa , I think Dynamic Segmentation

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[amsrivastavaa]

Hi @amitchandak ,

I gone through the video and its seems good however I have a bit different requirement than that

1. In my case, there is filter channel=1 which filters data and then I need to apply another logic.

2. After filteration, what ever Project left, I need to have data for all project with TYpe and Year and then matrix will form on top of that data.

Thanks

Amit

[amsrivastavaa]

Hi @amitchandak ,

I am able to achieve first part of query.

Could you please help me find solutions for second part as well.

I.e. once I have data with Table visual where Filter based on Measure is used, then how would I load data into another visual with only Project and Year where I have Measure Filter value =1.

Please suggest 

Thanks

Amit

[v-yadongf-msft]

Hi @amsrivastavaa ,

You can add data fields in filters pane. Can you tell me what's your problem in details?

Best regards,

Yadong Fang

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

[amsrivastavaa]

Hi @v-yadongf-msft ,

I have detailed my issue on below url, PFB the same.

https://community.powerbi.com/t5/Desktop/Power-BI/m-p/2876038#M990104

Please suggest

Thanks 

Amit