Help to construct Dax formula with conditional inputs

[juju]

I am looking to calculate the total cost of energy used based on the following usage buckets:

• energy used up to 300 kWh will cost a unit price is $0.x / kwh
• energy used greater than 300 kWh but less than or equal to 600 kwh will cost a unit price is $0.y / kwh
• energy used greater than 600 kWh will cost a unit price is $0.z / kwh

I have a table which shows daily total energy ussage. I need to set up a DAX formula which calculates the total daily cost by parsing that  total number into the buckets above and applying the unit costs at each level. I am totally new to DAX so struggling with how to set this up.

[vsteinly]

I have been trying to get you model to work for me using a summary based on the month and not just the day.  I thought it should have been as easy as pulling my month column from my calendar table instead of the day.  Any quick suggestions?

[juju]

---

@MattAllington wrote:

Do you really need the hourly data by day?  From what I understand so far, this is not needed.  I suggest you group by day and channel and sum the Value column.  That way you have 1 number by channel by day.   

---

@MattAllington  When calculating the cost , we dont really need the hourly data by day, however, hourly data is an important part of our analysis ( 24 hour usage profile ).  So perhaps, I should create a calculated summary table ?  Any issues about performance of calculated tables in case my dataset gets big?

[MattAllington]

I think it is OK to have it as long as you use it.  But I didn't see any time of day data in your sample - just multiple copies of the same day.  I suggest you split date and hour into 2 columns.  Maybe just use an integer for hours (0 - 23).

Did the proposal I made work for you?

[juju]

But I'd like to understand what is going on here : Thats the only part of the code I dont quite get. Especially the use of the $Amount .

 CALCULATE (
        ( SUM ( Summary[qty] ) - MAX ( RatesTable[USAGE FROM] ) )
            * MAX ( RatesTable[RATES] )
            + MAX ( RatesTable[$Amount] ),

[MattAllington]

It's the MAX that makes it confusing.  In the formula I wrote, there is only a single value for RatesTable[USAGE FROM], RatesTable[RATES] and RatesTable[$Amount] 

If I wrote it like this, you would probably understand it.

CALCULATE (
        (SUM ( Summary[qty] ) -  RatesTable[USAGE FROM] ) 
            *  RatesTable[RATES] 
            + RatesTable[$Amount] 

Add up the total Qty, subtract the usage from (which works out the incremental qty above the baseline, then multiply this incremental amount by the rate for the incremental, and finally add the lump sum.  All good.

The problem is that DAX doesn't allow you to refer to what I call "Naked columns" in a DAX expression unless there is a Row Context (this is a topic in its own right).  I have added MAX around each of these columns so they are no longer "Naked".  But given I have already filted the table so there is only 1 row, it returns the value of that row.  I could have used MIN, SUM, AVERAGE etc and they all would return the same result.  Yes it is confusing, but once you get the concept you become more powerful.

[juju]

Pretty advanced for a beginner! I need to read it a couple of times to get it.  I think my Rates[$Amount] values are wrong . I don't get the significance of the Rates[$Amount] - what exactly it does.

Thanks again.

[MattAllington]

Well this is not really a dax pint, it is the calculation logic.  rather than split the value into each bucket, subtract the amount and do the calculation, all you need to do is work out which band applies, and do a simple calc of $amount + rate X increment over base. In Australia, we use this for our tax tables. 

https://www.ato.gov.au/rates/individual-income-tax-rates/

[juju]

Yes it did! I was just going to post that! Thank you!!

[juju]

Hi @MattAllington

I have a table ( source data ) , which has dates and energy usage  on those dates. Then I have a rate schedule I am looking to use to calculate the MTD cost of energy on each day.  The rate schedule applies a different rate based on the MTD level of use - so for any particualr day, the MTD usage will be broken up into the usage buckets of 0-300, 300-600 and 600 + and the rates for each bucket applied accordingly to compute the total MTD cost. (plus VAT).

[MattAllington]

---

@juju wrote:

Hi @MattAllington

I have a table ( source data ) , which has dates and energy usage  on those dates. Then I have a rate schedule I am looking to use to calculate the MTD cost of energy on each day.  The rate schedule applies a different rate based on the MTD level of use - so for any particualr day, 

---

The "issue" is that originally you said "daily level" and now you are saying "MTD".    No harm done, just different.

[juju]

Agreed - apologies. I realized I needed the MTD calculation halfway through this discussion.  I will setup the calender table and apply the code and report back. Many thanks

[juju]

@MattAllington  Thanks for this. I will dig into it asap and report back.