Stock age measure range and show how many items in each range

[Jdsarmientoc]

Hi everyone!

Im currently trying to do an inventory age report.

First i wanted to calculate the inventory age (today - inbound date). (MEASURE 1)

Then i wanted to assign this result to a range of ages (MEASURE 2) and finally i want to create a table with the ranges and then show how many items are in that range of age (MEASURE 3).

Also i want to put a secondary table next to it of Items and # of units so when i click a range it filters the secondary table to show which items are on this range and how many units i have in stock of each item.

I've already managed to do the MEASURE 1 and MEASURE 2 like this, respectively:

On this first one i have to filter by warehouse because im only interested in some of the warehouses data, not all of them.

Antigüedad (días) = calculate(AVERAGEX('Inv Actual';today()-[Fecha última entrada]);FILTER('Inv Actual';'Inv Actual'[Bodega]="5003"||'Inv Actual'[Bodega]="PTT"||'Inv Actual'[Bodega]="BET"||'Inv Actual'[Bodega]="PTE"||'Inv Actual'[Bodega]="ACS"||'Inv Actual'[Bodega]="5001"||'Inv Actual'[Bodega]="5002");'Inv Actual'[Existencia]>0)

Rangos = switch(TRUE();
[Antigüedad (días)]=BLANK();BLANK();
[Antigüedad (días)]<30;"< 30";
[Antigüedad (días)]>=30 && [Antigüedad (días)] <90;"30-90";
[Antigüedad (días)]>=90 && [Antigüedad (días)] <180;"90-180";
[Antigüedad (días)]>=180 && [Antigüedad (días)] <360;"180-360")

 

Items is where i have all the information of the items and its related to all the other tables with the Item ID.

Now what i would like to do is to create a table visual with the Ranges as Rows and the <number of items in that range> as value but cant use a measure (MEASURE 2) as Rows. As soon as i drag the measure to the table it shows me the result of the measure for all my data.

In case my explanation is hard to understand ill show my amazing paint skills to show what i would like (left table i already have in BI, right table is in excel):

And whenever i click a range in the right table i want it to filter the left table to show which items are so if i click the 180-360 range it shows me those 7 items on the left table.

Thanks and hope you can help!

PS: As you can imagine english is not my first language so please excuse me on that.

David.

[v-yuta-msft]

@Jdsarmientoc ,

Just remove the column [Item Id] and [Existenca] from the table visual and check if the left is same with the right.

Regards,

Jimmy Tao