Forum Discussion

juju's avatar
juju
Helper III
9 years ago
Solved

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 ...
Need Help
  • MattAllington's avatar
    MattAllington
    9 years ago

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

     

     

MattAllington's avatar
MattAllington
Community Champion
9 years ago

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's avatar
juju
Helper III
9 years ago

 

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