Inventory Tracker DAX

[alex9999]

Hi, 

I am currently creating an inventory tracker which tracks items moving in or out of a unit. I have created the core functionality of the report. However, I have 300+ items that I need to make individual measures for. The measure so far looks like this:

Assuming the item has a base count of 1000:

Item = 1000 + CALCULATE(SUM('Inventory'[Quantity]), FILTER('Inventory', 'Inventory'[Material_In_True] = TRUE() && 'Inventory[Component] = "Item")) - CALCULATE(SUM('Inventory'[Quantity]), FILTER('Inventory', 'Inventory'[Material_In_True] = FALSE() && 'Inventory[Component] = "Item"))

 Essentially, I take the base value and plus and minus any entries of that component that is fed through a SharePoint list.

I am currently pulling my hair out creating a measure for every individual component. Furthermore, I will be calculating the weight of each component so I will be creating a measure for each individual items weight * quantity to get the total weight. I will be left with 600+ measures in this report.

Any help with a time saving solution will be appreciated.

[v-yetao1-msft]

Hi @alex9999 

Has your problem been solved ? If it has been solved, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Community Support Team _ Ailsa Tao

[BeaBF]

@alex9999 Hi!

What do you think about calculating a dynamic measure through the ALLSELECTED function and using a filter on the Item in order to calculate the measure related to that ?

BF

[alex9999]

Hi @BeaBF , 

The solution suggested will not work as I will eventually need to display items in a table so I can view all metrics of each component.

Can you suggest something else?

Kind regards

[BeaBF]

@alex9999 can you paste some data and an example of the expected result for two items? so as to better understand the request and do some tests

BF

[alex9999]

@BeaBF , my report will have data shown as such:

Date & Time	Supervisor	In/Out	Component	Quantity
28/02/2022 11:22	J. Bloggs	Out	Item A	30
28/02/2022 12:30	J. Bloggs	Out	Item B	45
28/02/2022 1:35	C. Jones	In	Item A	22
28/02/2022 2:20	J. Bloggs	In	Item B	20

This table is being fed through a SharePoint list that is being updated by a foreman who is tracking everything that moves in and out of the unit. Once we do an initial count of the unit and the items in it (which is represented by the first figure of the DAX measure), everything that goes into the unit will be added onto that number, while everything that moves out will be minused from that same figure - leaving us with an accurate figure of each component.

My issue was that I am having to create a measure for every single item individually and I think my computer is going to explode soon.

I'm not sure if there is an easier way to go about creating my report rather than having 600 measures for total quantity and total weight.

Thanks for any help, @BeaBF .

[BeaBF]

@alex9999 do you necessarily need a measure? instead create a column calculated with the if conditions? 

I'm trying to reason with you ...

BF

[alex9999]

Hi @BeaBF, 

Can you elaborate please?

I am new to Power BI so please don't take it as I am stubborn, I am just inexperienced.

[BeaBF]

@alex9999 Sure!

The main difference between calculated columns and measures is that columns are evaluated at each row, whereas measures are only evaluated at the level of granularity they are plotted at.

Calculating it through a calculated column, it then calculates row x row your expression, inserting some if conditions.
To help you concretely, on the example data you have reported above, can you try to write me two measures? I will try to simplify them into one.

BF