Calculate montlhy cost average broken down by month, item ID and expense type

[Anonymous]

Hello,

This is my firt post ; Thank you for having me I've been lurking around here for some time and today I don't know how to solve this problem. I'm still very new at PBI and still reading training books on PBI and DAX. 

I am currently working on a project where I'm trying to compute monthly average for:

- each item (item ID#)

- each particular month (I created a Month-year column - some item only stay online for a few months some keep going until the end of the year)

- summing up all expense codes in the monthly average cost but have the ability to filter monthly average by expense code to compare later in the dashboard.

So ideally I would be able to:

- calculate monthly average for each ItemID

- filter by expense code (to remove or add some of them as needed)

Here is my sample table

ID	Date	Month-year	Expense Code	Total
1	2021-06-15	6-2021	Code1	600
1	2021-06-15	6-2021	Code1	40
1	2021-06-15	6-2021	Code2	25
1	2021-06-15	6-2021	Code2	2
1	2021-06-15	6-2021	Code2	1
1	2021-06-15	6-2021	Code3	250
2	2021-05-23	5-2021	Code3	10
2	2021-05-15	5-2021	Code1	550
2	2021-05-15	5-2021	Code1	45
2	2021-05-15	5-2021	Code2	40
2	2021-05-15	5-2021	Code2	4
2	2021-05-15	5-2021	Code2	2
2	2021-05-15	5-2021	Code3	175
2	2021-05-14	5-2021	Code3	70
2	2021-05-02	5-2021	Code3	50

and my formula

Monthly cost  = 
SUMX(
    FILTER (
        table,
        table[ID] = EARLIER ( table[ID] )
            && DATEDIFF ( 'table'[Month-year], EARLIER ( 'table'[Month-year] ), MONTH ) = 0
    ),
    table[Total]
)

It's not too bad but some of the costs are wrong while some are right so I suspect that the costs breakdown are not working too well.

Thank you all for your help!

[amitchandak]

@Anonymous , Sumx or averagex as per need

A new column

Monthly cost =
AverageX(
FILTER (
table,
table[ID] = EARLIER ( table[ID] )
&& 'table'[Month-year] = EARLIER ( 'table'[Month-year] )
),
table[Total]
)

[Anonymous]

Hello,

So this formula is indeed calculating correct monthly cost as a whole but it is not able to "dynamically" recalculate the monthly cost when filtering by expense code from the report view. It does perform some calculation but the result is not correct - for instance it's telling me I pay more taxes as monthly cost than rent cost total which is not true.

Is it coming from the formula or maybe the way I setup the filter on the report page?

Again thank you for the help - at least it solved 50% of the problem.

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Create a measure as below;

Measure =
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ID] = MAX ( 'Table'[ID] )
                && 'Table'[Month-year] = MAX ( 'Table'[Month-year] )
        )
    )
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[Total] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ID] = MAX ( 'Table'[ID] )
                && 'Table'[Month-year] = MAX ( 'Table'[Month-year] )
        )
    )
RETURN
    DIVIDE ( _sum, _count, BLANK () )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!