cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
murali5431
Helper II
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

MonthCountOld (count*2.5)NewDifference (old-new) USD
Dec-2024376092.56950-857.5
Jan-2134098522.569501572.5
Feb-21200850206950-1930
Cumulative   -1215

 

Thanks in advance!

Muralidhar

1 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. 

View solution in original post

9 REPLIES 9
daxer-almighty
Solution Specialist
Solution Specialist

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.

@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

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.

 

Sample data.jpg

Thanks,

Muralidhar

Hi @murali5431 ,

 

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

 

stevedep_0-1614490683177.png

_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. 

 

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

Sample data.jpg

 

Thanks in advance!

 

Regards,

Muralidhar

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. 

View solution in original post

murali5431
Helper II
Helper II

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

 

Regards,

Muralidhar

Can you elaborate a bit more on your requirement?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors