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

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

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