Conditional Calculation

[#1]

Here is the problem.

Good morning. I have a fact table containing daily and hourly electricity consumption. In Excel (pre load) I added a column DayNIght with values of "Day or Night" dependent upon the timestamp - so a flag. I want to multiply Consumption by the correct rate in a related dimension table dependent upon the value of the flag. The relationships are fine but I can't get the conditional logic to work either with 'If" or Set Analysis. Can someone help, please?

Denis

[kaushiknsolanki]

Hi Denis,

It is not possible to understand where is the problem unless you give us some example of the data along with calculation which you want.

Regards,

Kaushik Solanki

[#3]

How can I best upload some rows from my tables? Will a screenshot be OK?

Many thanks,
Denis

[kaushiknsolanki]

You can create another Application with some sample data which can show us the issue which you are facing.

Below link will help about how to upload the sample file.

Uploading a Sample

Regards,

Kaushik Solanki

[#5]

Here is a sample app with limited data

Many thanks

Denis

[brunobertels]

Hi

your dimension DayRate is in the table CurrentRates

This table as a drop table statement in your script

use this dimension CurrentRates.DayRate

[#7]

‌sorry but I my not have explained properly. I understand the use if the fully qualified expression but that is not my problem.

I want to create a new masure or column that follows this pseudo code logic

if DayNight = day

sum the kWh for that selection or filter and multiply by currentRates.dayrate

else the same calculation but multiplied by NighRate.

does that make sense. I can't get either "if" or set expression to work

many thanks

DEnis

[brunobertels · Accepted solution]

Hi

Try this

in your sheet Debug

as new mesure :

if(DayNight='Day',

(sum((kWh )* /100)),

(sum((kWh )* /100))

)

Bruno

[#9]

Bruno - thanks very much. This works just as I need.

Best regards and Bonnes Fêtes.

Denis

[brunobertels]

Hi

I'am Glad to be helpfull

Joyeux Noël et Bonne fêtes également,

Bruno