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.

[BhaveshPatel]

Hi @juju

You can also use PowerQuery Custom Column (If..Else..then) condition to calculate the same suggested by @MattAllington.

[MattAllington]

The answer depend so on your table structure. In general you should try to write a measure (read here http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/)

then the general approach is to filter the table so it has the records you need, then do the calculation. In your case it may be a multi part filter as follows (for example)

=calculate(sum(table[qty]) * X ,table[qty] <= 300)  + calculate(sum(table[qty]) * Y ,table[qty] > 300 && table[qty] <= 600) + calculate(sum(table[qty]) * z ,table[qty] > 600)

you may may also like to consider writing separate measures so you can break out low usage, med usage and high usage parts, then add them all together for total usage

[juju]

My tables are setup as followings:

Table 1:

Date  / Daily total usage (kWh)

Table 2 :

date   / tariff  structure  /  cost

For table 2 and example would be:

'2017-01-01'  | 0-300   | 0.x $/Kwh
'2017-01-01'  | 301-600 | 0.x $/Kwh
'2017-01-01'  |  600+   | 0.x $/Kwh

So I am thinking of creating a completely new calculated table based on the proposed DAX formula. I will join by dates, but i suspect the format of the "tariff structure" column doesn't help ?

[MattAllington]

For table 2, does that mean you have another record for 2 Jan?  ie do you have a pricing record for each day?  Or is it 1 record for the whole year?  THe solution you build will depend on this.

Personally I would not create a new summarized table unless there is something specific reason to do it.

Here are a couple of articles on banding that you could take a look at - this should help you understand what you need

http://exceleratorbi.com.au/banding-in-dax/

http://exceleratorbi.com.au/conditional-columns-power-bi-desktop/

Regardless of the approach you take, you will need a "lower" and "upper" limit column in table 2.  You can split that out in Power Query

[juju]

@MattAllington  Your banding article was great.  The issue with my table 2 is, the tariffs can change - hence the dates.  A banding table like below will work great, but I am not sure how to account for instances when the tariff changes. When that happens, I still want to be able to use the values of the old tariffs to apply those rates during the dates when they were applicable.  The tariffs typically change about once every quarter. Any suggestions about how the banding table should be setup?  Below is my stab at it. 

Also , when using the banding table, how will this impact your suggested formula above? - I will probably have to add an ID column to my customer table ( not shown here ) 

[MattAllington]

Your attempted approach is directionally correct. You will need to have a from date and to date to be able to manage the different periods. The approach is the same as the bands.  You can use the following construct. 

Filter(table,[myDate]>=table[from] && [myDate] < table[to])

it is generally better (more efficient) to have 2 separate filters rather than nesting 4 && in a single filter statement 

[juju]

So do I add a new set of rows when the date changes? Like the screenshot below?

---

@MattAllington wrote:

it is generally better (more efficient) to have 2 separate filters rather than nesting 4 && in a single filter statement 

---

I am a bit confused by this statement. Is the formula below still relevant?

=calculate(sum(table[qty]) * X ,table[qty] <= 300)  + calculate(sum(table[qty]) * Y ,table[qty] > 300 && table[qty] <= 600) + calculate(sum(table[qty]) * z ,table[qty] > 600)

[MattAllington]

Yes, you need new sets of rows each time your rates change. 

The original formulas don't apply for the banding solution - you need to use the approach in my article but use it for a measure as follows (sorry for not being clearer before )

=
CALCULATE (
    SUM ( DataTable[qty] ) * MAX ( RatesTable[Rate] ),
    FILTER (
        RatesTable,
        DataTable[qty] < RatesTable[To]
            && DataTable[qty] >= RatesTable[from]
    ),
    FILTER (
        RatesTable,
        DataTable[date] < RatesTable[To Date]
            && DataTable[date] >= RatesTable[from date]
    )
)

 I haven't tested above but I think it will work.  You may need to tweak it.  Post back with some sample data if it doesnt' work and I will try to work it out

[juju]

I am very close. I modified the code to be:

Cost = 
CALCULATE (
    SUM ( DataTable[qty] ) * MAX ( tariffs[TARIFF] ),
    FILTER (
    	tariffs,
    	tariffs[TYPE] = "Commercial"
    ),
    FILTER (
        tariffs,
        DataTable[qty] < tariffs[USAGE To]
            && DataTable[qty]>= tariffs[USAGE FROM]
    ),
    FILTER (
        tariffs,
        DataTable[date] < tariffs[DATE TO]
            && DataTable[date] >= tariffs[DATE FROM]
    )
)

I get the following error 

A single value for column 'qty' in table 'DataTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

The datatable has a date and usage qty column so not sure why I get the error. 

Update:  This seems to work ( no errors when setting up the measure ) but not sure the code is correct ? The numbers seem to check out.

Cost = 
CALCULATE (
    SUM ( DataTable[qty] ) * MAX ( tariffs[TARIFF] ),
    FILTER (
    	tariffs,
    	tariffs[TYPE] = "Commercial"
    ),
    FILTER (
        tariffs,
        SUM( DataTable[qty] ) < tariffs[USAGE To]
            && SUM (DataTable[qty] ) >= tariffs[USAGE FROM]
    ),
    FILTER (
        tariffs,
        MAX (DataTable[date] ) < tariffs[DATE TO]
            && MAX (DataTable[date] ) >= tariffs[DATE FROM]
    )
)

Update 2 :  I wanted to compute this for a month-to-date value of the DataTable[qty] value so I modified the code to this: ( doesnt work ... ).

Cost = 
CALCULATE (
    CALCULATE ( SUM (DataTable[qty]), DATEMTD(DataTable[Date]) ) * MAX ( tariffs[TARIFF] ),
    FILTER (
    	tariffs,
    	tariffs[TYPE] = "Commercial"
    ),
    FILTER (
        tariffs,
        SUM( DataTable[qty] ) < tariffs[USAGE To]
            && SUM (DataTable[qty] ) >= tariffs[USAGE FROM]
    ),
    FILTER (
        tariffs,
        MAX (DataTable[date] ) < tariffs[DATE TO]
            && MAX (DataTable[date] ) >= tariffs[DATE FROM]
    )
)

[MattAllington]

The error is because datatable[qty] operates without a row context and hence it can't be used without an aggregation function (my bad). Your update 1 solves this problem. If it works, it works. 

Ok, but you want MTD. I think that changes everything. You will need a calendar table and you need to iterate over each day in the month, and for each day you will need to do the calculation. This is now past the tipping point where I would probably add a calculated column in the data table that returns the "rate" to charge for the row. This column is created using the same technique however a calc column has a row context. It is therefore much easier to calculate and return just the rate. Once the rate is in a column, the calculation qty * rate is very easy. This new calc column has a low cardinality and hence will compress well. 

So the calc column would work like I showed in my banding example posted above. You apply the filters To the rates table as you have done so that only a single row is visible. Then return max of the tarrif column as the resulting calculated column result. From there you can just write a new measure 

sumx(datatable,datatable[qty] * datatable[rate])

mtd should then work. 

[juju]

---

@MattAllington wrote:

... I would probably add a calculated column in the data table that returns the "rate" to charge for the row. This column is created using the same technique however a calc column has a row context. It is therefore much easier to calculate and return just the rate. Once the rate is in a column, the calculation qty * rate is very easy. This new calc column has a low cardinality and hence will compress well. 

Remember for each row, there is a sliding tariff scale.  So depending on the mtd value , multiple tariffs can be applied to one row of usage - so not sure returning a single tariff per row will work. I have attaached a link to an excel workbook on dropbox with sample data.

sample file
 
e.g  for a day of say MTD 602 kWH 
 
- first 299 will be calculated with a rate of $0.x (0-300)
- next 300 will be calculated with a rate of $0.y (300-600)
- final 3 kWh will have a rate of $0.z (600 +)

[MattAllington]

I understood that each row in your source data is evaluated against the usage consumption for that row.  Now I understand that it is a cumulative tarrif to be applied for the total usage during the month. Let me look at the sample data

[MattAllington]

OK, Try adding some custom MTD time intelligence to the formula I provided earlier

This formula requires you to have a MonthID column in your calendar table.  So the first month of the first year would be 1, the second month of the first year would be 2, the first month of the second year would be 13 and so on.

=
CALCULATE (
    SUM ( DataTable[qty] ) * MAX ( RatesTable[Rate] ),
    FILTER (
        RatesTable,
        DataTable[qty] < RatesTable[To]
            && DataTable[qty] >= RatesTable[from]
    ),
    FILTER (
        RatesTable,
        DataTable[date] < RatesTable[To Date]
            && DataTable[date] >= RatesTable[from date]
    ),
    FILTER (
        ALL ( Calendar ),
        Calendar[MonthID] = MAX ( Calendar[MonthID] )
            && Calendar[Date] <= MAX ( Calendar[Date] )
    )
 

[juju]

---

@MattAllington wrote:

OK, Try adding some custom MTD time intelligence to the formula I provided earlier

This formula requires you to have a MonthID column in your calendar table.  So the first month of the first year would be 1, the second month of the first year would be 2, the first month of the second year would be 13 and so on.

=
CALCULATE (
    SUM ( DataTable[qty] ) * MAX ( RatesTable[Rate] ),
    FILTER (
        RatesTable,
        DataTable[qty] < RatesTable[To]
            && DataTable[qty] >= RatesTable[from]
    ),
    FILTER (
        RatesTable,
        DataTable[date] < RatesTable[To Date]
            && DataTable[date] >= RatesTable[from date]
    ),
    FILTER (
        ALL ( Calendar ),
        Calendar[MonthID] = MAX ( Calendar[MonthID] )
            && Calendar[Date] <= MAX ( Calendar[Date] )
    )
 

---

@MattAllington  The above code still doesn't quite do what I am looking for. To illustrate, the code below works - if I hard code everything. I think your code does not account for the way each MTD number has to be broken up into the specific usage bands and a different rate applied for each band.  Your code seems to look for one tariff that matches the MTD number - and applies one calculation, without regard for all the tiers in that one MTD number. 

MTD Cost = 
IF (
	DataTable[MTD Value] <= 300 , 
	CALCULATE ( DataTable[MTD Value]  * 0.9679), 
	IF (
		DataTable[MTD Value] > 300 && DataTable[MTD Value] <= 600,
		CALCULATE( 300 * 0.9679 ) +
		CALCULATE(  ( DataTable[MTD Value]- 300 )  * 1.03 ),
		IF (
			DataTable[MTD Value] > 600,
			CALCULATE( 300 * 0.9679 ) +
			CALCULATE( 300 * 1.03 ) +
			CALCULATE(  ( DataTable[MTD Value]- 600 )  * 1.6251 )
			,
			0
		)
	)
)

[MattAllington]

Yes, you are right, sorry. &nbsp;I think a slightly different approach is in order. &nbsp;In the rates table you should add a $Amount column as well as the rate columns you already have. &nbsp;The $Amount column will have the lump sum payable for the qty prior to the rate kicking in. &nbsp;So in the 0 - 300 row, the $Amount would be $0. &nbsp;In the 300 - 600 row it would be 300 times the rate payable up to 300 (payable as a lump). &nbsp; &nbsp;Doing it this way means you can do a more simple calc in the DAX &nbsp;Then you need to restrucutre my formula from before so that it first filters for the MTD calc, then after that it applies the rate calculations. &nbsp;Here is my guess (keeping in mind I don't have your data model infront of me.
&nbsp;
=
CALCULATE (
CALCULATE (
(SUM ( DataTable[qty] ) - max(RatesTable[From])) * MAX ( RatesTable[Rate] )
+ MAX ( RatesTable[$Amount] ),
FILTER (
RatesTable,
Sum(DataTable[qty]) < RatesTable[To]
&& sum(DataTable[qty]) >= RatesTable[from]
),
FILTER (
RatesTable,
DataTable[date] < RatesTable[To Date]
&& DataTable[date] >= RatesTable[from date]
)
),
FILTER (
ALL ( Calendar ),
Calendar[MonthID] = MAX ( Calendar[MonthID] )
&& Calendar[Date] <= MAX ( Calendar[Date] )
)
)
Sorry I can't test it, but I think it is right. You may need to tweak it.

Let me know
&nbsp;

Note I just made an edit

[juju]

@MattAllington  I have kicked it around a fair bit today - still cant get it to work. So attaching my datafile here with sample data. Maybe it will help.  Many thanks. Much appreciated.

Sample PBIX file

[MattAllington]

Your data model doesn't have a QTY column as mentioned in the other posts.  I see a value column in the data table but it had integers from 0 to 16, which doesn't seem to be related to the probelm you have desribed.  

[juju]

The value column is my qty column. I change the code from [qty] to [value] when testing your code. Also the values are correct - from 0 to about 15 kWh per hour . So I am looking to use the daily MTD calculation for the [value] field to calculate the cost using the tariff structure . 

[MattAllington · Accepted solution]

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.   

I have created a summary table using DAX.  You should load your data using Power Query so it is in summary format (not by hour) and not use what I have done.  I have only done this because I don't have access to your source data.

Once I created the summary table, I copied my formula and applied the table name changes.  There was a missing aggregator which I have fixed.  It seems to work.

Here is the file

https://www.dropbox.com/s/t6p9f6mtwu12y2g/Datafile2.pbix?dl=0

Here is the corrected formula

test =
CALCULATE (
    CALCULATE (
        ( SUM ( Summary[qty] ) - MAX ( RatesTable[USAGE FROM] ) )
            * MAX ( RatesTable[RATES] )
            + MAX ( RatesTable[$Amount] ),
        FILTER (
            RatesTable,
            SUM ( Summary[qty] ) < RatesTable[USAGE TO]
                && SUM ( Summary[qty] ) >= RatesTable[USAGE FROM]
        ),
        FILTER (
            RatesTable,
            MAX ( Summary[Date] ) < RatesTable[DATE TO]
                && MAX ( Summary[date] ) >= RatesTable[DATE FROM]
        )
    ),
    FILTER (
        ALL ( Calendar ),
        Calendar[MonthID] = MAX ( Calendar[MonthID] )
            && Calendar[Date] <= MAX ( Calendar[Date] )
    )
)

[vsteinly]

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