Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
murali5431
Helper III
Helper III

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 Sage
Solution Sage

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. 

murali5431
Helper III
Helper III

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

 

Regards,

Muralidhar

Can you elaborate a bit more on your requirement?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors