Calculation of month-wise cost

[murali5431]

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

Thanks in advance!

Muralidhar

[daxer-almighty]

Hi @murali5431 

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.

[murali5431]

@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

[stevedep]

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

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

[murali5431]

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

[stevedep]

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. 

[murali5431]

@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)

Thanks in advance!

Regards,

Muralidhar

[stevedep · Accepted solution]

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. 

[murali5431]

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

Regards,

Muralidhar

[stevedep]

Can you elaborate a bit more on your requirement?