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, 2020 in Power BI by anonymous
• 120 points
2,740 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!!

If you are interested in learning Power BI, check out Power BI Course online now!

answered Sep 28, 2020 by Gitika
• 65,770 points

Related Questions In Power BI

0 votes
1 answer

How to calculate average percentage in Power BI?

You can add one more measure to ...READ MORE

answered Feb 23, 2022 in Power BI by CoolCoder
• 4,420 points
6,024 views
0 votes
1 answer

How do I calculate a rolling average or cumulative total in Power BI without performance issues?

When working in Power BI, especially with ...READ MORE

answered Nov 7 in Power BI by pooja
• 8,470 points
59 views
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
15,668 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,020 points
2,935 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,130 points
1,350 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 22, 2018 in Power BI by nirvana
• 3,130 points
2,746 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,520 points
1,519 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
• 52,350 points
1,667 views
0 votes
1 answer

Using DAX calculation how to calculate monthly budget till today in power bi Desktop?

You can make use of this: MTD Budget ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,770 points
1,857 views
0 votes
1 answer

How to measure in DAX to calculate YTD for chosen month only for Power BI?

If you use the date column from FactTable, ...READ MORE

answered Dec 22, 2020 in Power BI by Gitika
• 65,770 points
4,891 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP