Calculate average over monthly sums

[Robert1981]

Hi,
I am trying to calculate the Inventory Turnover for one of our businesses. I am not sure how to build the dax in this case. In my data I have several levels of business/product information and I have different periods. The goal is to calculate the inventory total month average, for any level I put in my report.

Below is a sample of the data.

The goal is to calculate set up a formula that calculates the average interest in my matrix, taking into account the level I bring into the report and the date references (month, quarter, year) that I select.

Right now my formula keeps doing the average of all the individual values and not of the sum of the values for the specific month.

total Corp	Business group	Product Family	Product	Date	Value
Corp	Business1	Family1	Prod1	1/31/2023	100
Corp	Business1	Family1	Prod2	1/31/2023	15
Corp	Business1	Family2	ProdA	1/31/2023	45
Corp	Business1	Family2	ProdB	1/31/2023	55
Corp	Business2	Family3	Prod1	1/31/2023	250
Corp	Business2	Family3	Prod2	1/31/2023	300
Corp	Business2	Family4	ProdA	1/31/2023	375
Corp	Business2	Family4	ProdB	1/31/2023	400
Corp	Business1	Family1	Prod1	2/28/2023	105
Corp	Business1	Family1	Prod2	2/28/2023	25
Corp	Business1	Family2	ProdA	2/28/2023	40
Corp	Business1	Family2	ProdB	2/28/2023	65
Corp	Business2	Family3	Prod1	2/28/2023	260
Corp	Business2	Family3	Prod2	2/28/2023	315
Corp	Business2	Family4	ProdA	2/28/2023	375
Corp	Business2	Family4	ProdB	2/28/2023	400

[Anonymous]

Hi @Robert1981 

You can try the following steps.

1. Create a calculated table as follows and create a slicer based on this table

Date = VALUES('Table'[Date])

2. Create a measure as follows

valueaverage = 
CALCULATE (
    AVERAGE ( 'Table'[Value] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Business group] ),
        YEAR ( 'Table'[Date] ) = YEAR ( SELECTEDVALUE ( 'Date'[Date] ) )
            && MONTH ( 'Table'[Date] ) = MONTH ( SELECTEDVALUE ( 'Date'[Date] ) )
    )
)

Is this the result you expect?

If I've misunderstood you, please provide detailed sample data and the results you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Community Support Team _Yuliax

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

[Robert1981]

Not exactly:
What I am trying to accomplish is that:
When I display months - the formula will do (sum of inventory specific month + sum of inventory previous month) / 2
When I display quarters - the formula will do (sum of inventory last month of quarter + sum of inventory last month of previous quarter) / 2  
Or it can take the average of all the sums per month within each quarter

I tried to extend the dataset a bit to show what I mean:

		1/31/2023	2/28/2023	3/31/2023	4/30/2023	5/31/2023	6/30/2023
Sum	Family1	115	130	115	126.5	143	126.5
	Family2	100	105	105	110	115.5	115.5
Ave	Family1		122.5	122.5	120.75	134.75	134.75
	Family2		102.5	105	107.5	112.75	115.5

		Q1	Q2
Sum	Family1	115	126.5
	Family2	105	115.5
Ave	Family1		120.75
	Family2		110.25

otal Corp	Business group	Product Family	Product	Date	Quarter	Value
Corp	Business1	Family1	Prod1	1/31/2023	Q1	100.00
Corp	Business1	Family1	Prod2	1/31/2023	Q1	15.00
Corp	Business1	Family2	ProdA	1/31/2023	Q1	45.00
Corp	Business1	Family2	ProdB	1/31/2023	Q1	55.00
Corp	Business2	Family3	Prod1	1/31/2023	Q1	250.00
Corp	Business2	Family3	Prod2	1/31/2023	Q1	300.00
Corp	Business2	Family4	ProdA	1/31/2023	Q1	375.00
Corp	Business2	Family4	ProdB	1/31/2023	Q1	400.00
Corp	Business1	Family1	Prod1	2/28/2023	Q1	105.00
Corp	Business1	Family1	Prod2	2/28/2023	Q1	25.00
Corp	Business1	Family2	ProdA	2/28/2023	Q1	40.00
Corp	Business1	Family2	ProdB	2/28/2023	Q1	65.00
Corp	Business2	Family3	Prod1	2/28/2023	Q1	260.00
Corp	Business2	Family3	Prod2	2/28/2023	Q1	315.00
Corp	Business2	Family4	ProdA	2/28/2023	Q1	375.00
Corp	Business2	Family4	ProdB	2/28/2023	Q1	400.00
Corp	Business1	Family1	Prod1	3/31/2023	Q1	95.00
Corp	Business1	Family1	Prod2	3/31/2023	Q1	20.00
Corp	Business1	Family2	ProdA	3/31/2023	Q1	25.00
Corp	Business1	Family2	ProdB	3/31/2023	Q1	80.00
Corp	Business2	Family3	Prod1	3/31/2023	Q1	270.00
Corp	Business2	Family3	Prod2	3/31/2023	Q1	330.00
Corp	Business2	Family4	ProdA	3/31/2023	Q1	340.00
Corp	Business2	Family4	ProdB	3/31/2023	Q1	380.00
Corp	Business1	Family1	Prod1	4/30/2023	Q2	110.00
Corp	Business1	Family1	Prod2	4/30/2023	Q2	16.50
Corp	Business1	Family2	ProdA	4/30/2023	Q2	49.50
Corp	Business1	Family2	ProdB	4/30/2023	Q2	60.50
Corp	Business2	Family3	Prod1	4/30/2023	Q2	275.00
Corp	Business2	Family3	Prod2	4/30/2023	Q2	330.00
Corp	Business2	Family4	ProdA	4/30/2023	Q2	412.50
Corp	Business2	Family4	ProdB	4/30/2023	Q2	440.00
Corp	Business1	Family1	Prod1	5/31/2023	Q2	115.50
Corp	Business1	Family1	Prod2	5/31/2023	Q2	27.50
Corp	Business1	Family2	ProdA	5/31/2023	Q2	44.00
Corp	Business1	Family2	ProdB	5/31/2023	Q2	71.50
Corp	Business2	Family3	Prod1	5/31/2023	Q2	286.00
Corp	Business2	Family3	Prod2	5/31/2023	Q2	346.50
Corp	Business2	Family4	ProdA	5/31/2023	Q2	412.50
Corp	Business2	Family4	ProdB	5/31/2023	Q2	440.00
Corp	Business1	Family1	Prod1	6/30/2023	Q2	104.50
Corp	Business1	Family1	Prod2	6/30/2023	Q2	22.00
Corp	Business1	Family2	ProdA	6/30/2023	Q2	27.50
Corp	Business1	Family2	ProdB	6/30/2023	Q2	88.00
Corp	Business2	Family3	Prod1	6/30/2023	Q2	297.00
Corp	Business2	Family3	Prod2	6/30/2023	Q2	363.00
Corp	Business2	Family4	ProdA	6/30/2023	Q2	374.00
Corp	Business2	Family4	ProdB	6/30/2023	Q2	418.00

[Robert1981 · Accepted solution]

Problem solved by standarzing the KPI. Every month's KPI is calculated using the 3-month moving average. Now it doesn't matter anymore if I looking at a quarter or single month. The calculation is always the same

[amitchandak]

@Robert1981 , Suppose you want to add till day and then Avg , prefer to use date table joined iwth date and use date , month year from date table

Averagex(Summarize(Table, Date[Date], "_1", Sum(Table[Qty]) ), [_1])

Avg over Product Date

Averagex(Summarize(Table, Table[Product], Date[Date], "_1", Sum(Table[Qty]) ), [_1])

[Daniel29195]

can you please share the visual and the formula you are using  ?