Help to construct Dax formula with conditional inputs

---

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?

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?

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

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] ),

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.

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.

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/

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?

Hey Matt,

I am trying your model for our needs,  We have a four tiered rate structure and your model looked like it would fit the bill.  So I created a model with our data and keep getting the charge amount to read only one line in the rates table.  Its not going though the rows and create a calulated value.  Here is my model.  I would appreciate any insights.

https://drive.google.com/file/d/1hRrsF2j_Ixm5PHSPrrekm8Rz4zFlmp_R/view?usp=sharing