cancel
Showing results for
Did you mean:
Helper II

## Calculation of month-wise cost

Hi,

I am calculating cost for entry based on below logic:

Minimum fixed cost is \$6950 for 8000 entries. \$0.70 an entry to be incurred once 8000 entries threshold is hit for the month.

Calculation is as below and my objective is to obtain the cumulative value in a card. Could you please help me with the DAX I could use for this

 Month Count Old (count*2.5) New Difference (old-new) USD Dec-20 2437 6092.5 6950 -857.5 Jan-21 3409 8522.5 6950 1572.5 Feb-21 2008 5020 6950 -1930 Cumulative -1215

Muralidhar

1 ACCEPTED SOLUTION
Super User I

Hi,

I would expect it to be fine since values is working with month aggregation level, see highlighted below.

_OldvsNew SUMX(VALUES('Table'[Month].[Month]), [_OldCost]-[_NewCost])

Kind regards, Steve.

Proud to be a Super User!

9 REPLIES 9
Solution Sage

It's easy: If you don't get answers, that means your question is not clear. Rephrase your problem so that people can clearly understand it. Then you'll most likely get a good solution.

Easy.

Helper II

@daxer-almighty @stevedep
Thanks for the response and apologize if query wasn't clear.

As per the table above, I need to calculate diffrence of new cost & old cost for entries.

Old cost - total number of entries in a month * USD 2.5

New Cost - \$6950 for upto 8000 entries in a month. If entries are above 8000 in the same month, cost would be added at USD 0.7 per entry.

For example -

5000 entries - cost USD 6950

6000 entries - cost USD 6950

8010 entries - cost USD 6950 + (8010-8000)*0.7 = 6957 USD

My objective is to calculate savings in a month, each month and add up the values to find the total savings / loss (Old  cost - New cost) as of date. As per above table

Dec-20 - has 2437 entries

Old cost = 2437*2.5, New cost = 6950 (as volume less than 8k)

Savings/loss = Old  cost - New cost = 2437*2.5 - 6950 = (-)857.5

Jan-21 - 3409 entries, savings/loss = 1572.50 USD

Feb-21 - 2008 entries, savings/loss = (-)1930 USD

This gets calculated every month. I need a DAX which would calculate total loss / savings, which is (-)857.5 + 1572.50 + (-)1930 USD = (-)1215 USD (as of Feb-21) and display the value in a card.

Hope this explains. Thanks for your assistance.

Regards,

Muralidhar

Super User I

Can you provide a simple sample dataset with the expected outcome?

Proud to be a Super User!

Helper II

Hi.. Does below dataset help? Adding the last column which includes negative and  positive values, expected outcome is USD 34799.40.

I am attaching screenshot of sample data, as I am facing error with tables.

Thanks,

Muralidhar

Super User I

Hi @murali5431 ,

Here you go: p.s. your calculation for nov was off?

``````_OldCost	SUM('Table'[Count ofentries]) * 2.5
_NewCost	IF(NOT(ISBLANK(SUM('Table'[Count ofentries]))),
IF(SUM('Table'[Count ofentries]) <= 8000, 6950, ((SUM('Table'[Count ofentries])-8000)*0.7)+6950),BLANK())
_OldvsNew	SUMX(VALUES('Table'[Month].[Month]),  [_OldCost]-[_NewCost])
``````

File is attached.

Kind regards,

Steve.

Proud to be a Super User!

Helper II

@stevedep Thanks a lot for the response!

How can the DAX be modified, If monthly count is broken up into multiple rows, rather than being in a single row. I have taken only data for Dec-20, Jan-21 and Feb-21. My apologies, I am unable to attach the sample file.

Monthly sum remain the same (Dec20- 2437, Jan21 - 3409, Feb21 - 2008)

Regards,

Muralidhar

Super User I

Hi,

I would expect it to be fine since values is working with month aggregation level, see highlighted below.

_OldvsNew SUMX(VALUES('Table'[Month].[Month]), [_OldCost]-[_NewCost])

Kind regards, Steve.

Proud to be a Super User!

Helper II

Hi.. Could someone assist with my query? Thanks a lot!

Regards,

Muralidhar

Super User I

Can you elaborate a bit more on your requirement?

Proud to be a Super User!

Announcements