Forum Discussion
Help to construct Dax formula with conditional inputs
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.
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] ) ) )
32 Replies
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
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 ?
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
Hi juju
You can also use PowerQuery Custom Column (If..Else..then) condition to calculate the same suggested by MattAllington.
