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

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

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'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

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

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

       

      • MattAllington's avatar
        MattAllington
        Community Champion
        9 years ago

        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.