How to calculate average inventory in power bi?

0 votes
I've a scenario, I need to calculate average inventory per months where we have formula of average inventory is (beginning inventory + closing inventory)/2. if I select any month, then the beginning inventory should have the inventory amount of selected month and closing inventory should have the inventory amount of the last month. After that we will use the above listed formula for calculation. Can anyone help me to achieve this end result in power bi?
Sep 28 in Power BI by anonymous
• 120 points
36 views

1 answer to this question.

0 votes

Hi @There,

Is there any index field in your table? If this is a case, you can write a formula to use the index to find out the corresponding begin value and end value, then use them to get the average. 

AVG Measure =
VAR _start =
    MINX ( ALLSELECTED ( Table[index] ), [index] )
VAR _end =
    MAXX ( ALLSELECTED ( Table[index] ), [index] )
RETURN
    DIVIDE (
        LOOKUPVALUE ( Table[Amount], Table[index], _start )
            + LOOKUPVALUE ( Table[Amount], Table[index], _end ),
        2,
        BLANK ()
    )

Otherwise, you need to add an index column first.(power bi data model does not contain a row/column index to find out first or last row value).

Hope this helps!!

answered Sep 28 by Gitika
• 41,360 points

Related Questions In Power BI

0 votes
1 answer

To Calculate the Standard Deviation in Power BI

Suppose you've to obtain the Standard Deviation ...READ MORE

answered Nov 26, 2018 in Power BI by Upasana
• 8,620 points
7,561 views
0 votes
1 answer

How to combine your data or tables in Power BI?

Power BI includes Query Editor which is ...READ MORE

answered Feb 27, 2019 in Power BI by Phalguni
• 1,010 points
664 views
0 votes
1 answer

How to Install Power BI in Windows

Hi, Anitha Go to this Link https://powerbi.microsoft.com/en-us/downloads/ Then click on ...READ MORE

answered Feb 28, 2019 in Power BI by Cherukuri
• 32,490 points
113 views
0 votes
1 answer

How to create final table based on Joins of two tables in power BI?

Hi Nithin, It is easy and simple. 1. Go ...READ MORE

answered Mar 1, 2019 in Power BI by Cherukuri
• 32,490 points
9,174 views
0 votes
1 answer

Displaying Table Schema using Power BI with Azure IoT Hub

Answering your first question, Event Hubs are ...READ MORE

answered Aug 1, 2018 in IoT (Internet of Things) by nirvana
• 3,120 points
241 views
+1 vote
1 answer

Unable to install connector for Power Bi and PostgreSQL

I think the problem is not at ...READ MORE

answered Aug 21, 2018 in Power BI by nirvana
• 3,120 points
1,265 views
+2 votes
2 answers

Migrate power bi collection to power bi embedded

I agree with Kalgi, this method is ...READ MORE

answered Oct 11, 2018 in Power BI by Hannah
• 18,410 points
331 views
+1 vote
1 answer

Connect power bi desktop to dataset and create custom reports

Yes using Power BI REST API to ...READ MORE

answered Sep 18, 2018 in Power BI by Kalgi
• 51,970 points
340 views
0 votes
1 answer
0 votes
1 answer

How to replace Blank with "0" in Power BI Visual?

Hi, Sujit You can do this in two ...READ MORE

answered Feb 7 in Power BI by Gitika
• 41,360 points
11,566 views