Sum of a Min measure

[Anonymous]

Hi,

After searching other answers in the forum I couldn't find something that solved my particular problem.

I have a resource which is consumed on an hourly basis, but is priced on a daily basis with the first price being relevant up to the first 12,000 units and the remainder gets a second price. In order to divide the two I made a the following measures:

Daily Consumption = CALCULATE(SUM(Table[Hourly Consumption]), Filter(Table, [Date]))

1st price = MIN([Daily Consumption], 12000)

2nd price = [Daily Consumption] - [1st price]

Then I put the data in two different charts - daily and monthly.

On the daily chart the 1st price is presented correctly (each day gets its correct data up to 12,000), but on the monthly it brings back 12,000 for the whole month, without summarizing all the days.

When I tried to correct this I ended up with the opposite result - monthly aggregation was ok but the daily value just took the entire daily consumption.

Help would be much appreciated.

Thank you,

Oren

[v-lionel-msft]

Hi @Anonymous ,

I tried to reproduce your problem, but I couldn’t.

“I have a resource which is consumed on an hourly basis, but is priced on a daily basis with the first price being relevant up to the first 12,000 units and the remainder gets a second price.”

Let me guess what you want :

First, you want to sum [Hourly Consumption]  column by each day?

Second, do you want to get the minimum aggregate value from the table before the 12,000 rows?

Or you just want to get the each aggregate value of each row?

Then, do you want to add the measure to the “Daily table”?
But what’s the date of the “Daily table”?

Third, do you want to add the aggregate value from the table after the remaining rows to “Monthly table”?

But what’s the date of the “Monthly table”?

Please give me a sample data table and tell your specific needs and maybe I can help you.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lionel-msft ,

This is what my data and results look like:

As you can see in the second table, 'Daily Consumption' is the sum of each day's consumption, which is further divided between the first and second contracts for each day.

However, in the last table where I filter by month it doesn't sum up each contract but rather runs the MIN formula on the total I guess.

Hope this clears the issue for you. Thanks for you help.

Oren

[v-lionel-msft]

Hi @Anonymous ,

Becaues the total of the [1st Contract] is wrong.

The answer solved the issue:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lionel-msft ,

I was just going over that solution today and couldn't get it to work 😕.

1st Contract = SUMX(FILTER(VALUES(Table[Month]),[Daily Consumption]>12000),[Daily Consumption]-[2nd Contract])

2nd Contract = SUMX(FILTER(VALUES(Table[Month]),[Daily Consumption]>12000),[Daily Consumption]-12000)

Tried to play around with this in several ways but no progress.

Oren

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Maybe you can try this measures:

//Column
Date = 
DATEVALUE(
    FORMAT( [DateFullTime], "mm/dd/yyyy")
     ) 

//Measure
Daily Consumption = 
CALCULATE(
    SUM('Table'[Hourly_Consumption]),
    ALLEXCEPT(
        'Table',
        'Table'[Date]
    )
)
1st Contract = 
IF(
    HASONEFILTER([Date]),
    MIN([Daily Consumption], 1100),
    SUMX(
        VALUES('Table'[Date]),
        MIN([Daily Consumption], 1100)
    )
)
2st Contract = [Daily Consumption] -[1st Contract] 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.