topic Re: Iterative row calculation in DAX Commands and Tips

Iterative row calculation

HowAreYou — Wed, 09 Oct 2024 06:07:03 GMT

Hello community!

Could you please help me to understand and create suitable calculation for this case?

I have fotovoltaics where I generate electricity and I have also 2 locations where I consume electricity.

Data looks like this - 2 tables with DateTime relation:

DateTime	Location	Consumption (kW)	DateTime	Generation (kW)
1.1.24 9:00	A	3,25	1.1.24 9:00	0,25
1.1.24 9:15	A	3	1.1.24 9:15	0,25
1.1.24 9:30	A	3,5	1.1.24 9:30	0,5
1.1.24 9:45	A	4	1.1.24 9:45	1
1.1.24 10:00	A	3,75	1.1.24 10:00	3
1.1.24 10:15	A	4	1.1.24 10:15	6
1.1.24 10:30	A	3	1.1.24 10:30	7
1.1.24 10:45	A	2,75	1.1.24 10:45	8
1.1.24 11:00	A	3	1.1.24 11:00	2
1.1.24 11:15	A	4,75	1.1.24 11:15	1
1.1.24 11:30	A	5,5	1.1.24 11:30	0,25
1.1.24 11:45	A	6	1.1.24 11:45	0,5
1.1.24 12:00	A	3	1.1.24 12:00	0,25
1.1.24 9:00	B	0,25
1.1.24 9:15	B	0,5
1.1.24 9:30	B	0,25
1.1.24 9:45	B	0,75
1.1.24 10:00	B	1
1.1.24 10:15	B	0,5
1.1.24 10:30	B	0
1.1.24 10:45	B	0
1.1.24 11:00	B	1
1.1.24 11:15	B	0,25
1.1.24 11:30	B	1
1.1.24 11:45	B	0,25
1.1.24 12:00	B	0,5

The output I need should look like:

DateTime	Location	Consumption (kW)	% Covered
1.1.24 9:00	A	3,25	7,69%
1.1.24 9:15	A	3	8,33%
1.1.24 9:30	A	3,5	14,29%
1.1.24 9:45	A	4	25,00%
1.1.24 10:00	A	3,75	80,00%
1.1.24 10:15	A	4	100,00%
1.1.24 10:30	A	3	100,00%
1.1.24 10:45	A	2,75	100,00%
1.1.24 11:00	A	3	66,67%
1.1.24 11:15	A	4,75	21,05%
1.1.24 11:30	A	5,5	4,55%
1.1.24 11:45	A	6	8,33%
1.1.24 12:00	A	3	8,33%
1.1.24 9:00	B	0,25	100,00%
1.1.24 9:15	B	0,5	50,00%
1.1.24 9:30	B	0,25	100,00%
1.1.24 9:45	B	0,75	100,00%
1.1.24 10:00	B	1	100,00%
1.1.24 10:15	B	0,5	100,00%
1.1.24 10:30	B	0	100,00%
1.1.24 10:45	B	0	100,00%
1.1.24 11:00	B	1	100,00%
1.1.24 11:15	B	0,25	100,00%
1.1.24 11:30	B	1	25,00%
1.1.24 11:45	B	0,25	100,00%
1.1.24 12:00	B	0,5	50,00%

Additionally I would like to have a slicer with predefined multipliers which can multiply fotovoltaics generation based on selected value. Lets say that table above is default but I can choose 2, 3, 4, 5 etc. to multiply its generation by that number.

Im having the issue that calculated columns arent affected by this slicer and I wasnt able to create a measure which could calculate the above % coverage based on each individual row evaluation. It always sums everyhting together and doesnt count individually on 15 minutes basis.

Any ideas appreciated!

Thank you

Re: Iterative row calculation

Kedar_Pande — Wed, 09 Oct 2024 07:25:26 GMT

Establish a relationship between the two tables on the DateTime field.

Create a Table for Multipliers:

Multipliers = DATATABLE("Multiplier", INTEGER, {{1}, {2}, {3}, {4}, {5}})

Add a slicer to your report using this new Multipliers table.

Measure for Adjusted Generation:

Adjusted Generation (kW) = 
VAR SelectedMultiplier = SELECTEDVALUE(Multipliers[Multiplier], 1)
RETURN
SUMX(GENERATIONTABLE, GENERATIONTABLE[Generation (kW)] * SelectedMultiplier)

Measure for Percentage Covered

% Covered = 
VAR CurrentConsumption = SUMX(CONSUMPTIONTABLE, CONSUMPTIONTABLE[Consumption (kW)])
VAR CurrentGeneration = 
CALCULATE(
[Adjusted Generation (kW)],
FILTER(GENERATIONTABLE, GENERATIONTABLE[DateTime] = MAX(CONSUMPTIONTABLE[DateTime]))
)
RETURN
IF(CurrentConsumption > 0, DIVIDE(CurrentGeneration, CurrentConsumption, 0), 0)

Format the % Covered measure as a percentage in Power BI by going to the measure properties and setting the data type to percentage.

Re: Iterative row calculation

Anonymous — Thu, 10 Oct 2024 06:09:41 GMT

Hi HowAreYou ,

Thanks for the reply from Kedar_Pande , please allow me to provide another insight:

You can try the following steps.

1. Create a table of calculations to be subsequently used as slicer values.

Multipliers = GENERATESERIES(1, 5, 1)

2. create the measure.

% Covered = VAR TotalGen = CALCULATE(SUM('Table 2'[Generation (kW)]),FILTER(ALL('Table 2'),'Table 2'[DateTime] = MAX('Table'[DateTime]))) VAR TotalCons = SUM('Table'[Consumption (kW)]) VAR Div = DIVIDE(TotalGen * SELECTEDVALUE(Multipliers[Value]),TotalCons,1) RETURN IF(Div > 1, 1, Div)

The final result is shown below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

Re: Iterative row calculation

HowAreYou — Thu, 10 Oct 2024 19:05:15 GMT

Hello Anonymous! Kedar_Pande !

Thank you both for providing the guide. It works well. One additional question.

In my provided example there are just few timestamp rows but in reality there are thousands, maybe milions of rows.

Is it possible to have a measure which could evaluate % Covered based on every row and then make a sum (for example I want to group % Covered based on months (and for example see that 30% of month can be covered while not going into granilarity of 15 mins detail)).

*In your current solution calculation works only if dimmension is in the lowest granularity level

Sample of grouping issue:

Variable "TotalGen" in "% Covered" measure takes only "max('Table'[DateTime])" one row.

Re: Iterative row calculation

Anonymous — Fri, 11 Oct 2024 07:29:39 GMT

Hi HowAreYou ,

You can try the following expression.

TotalGen = SUM('Table 2'[Generation (kW)]) * SELECTEDVALUE(Multipliers[Value])

TotalCon = SUM('Table'[Consumption (kW)])

Measure = VAR _div = DIVIDE([TotalGen],[TotalCon],1) RETURN IF(_div > 1, 1, _div)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

Re: Iterative row calculation

HowAreYou — Mon, 14 Oct 2024 06:00:19 GMT

Hello Anonymous,

Thank you for providing the above. Thats not exactly it. Expected output should be:

It can be achieved by creating calculated column. The issue is how to apply multiplier to calculated column since its being calculated during the refresh and measure (or slicer) cannot be used within calc column.

Calc column is:

Covered (Multiplier 1) = IF ( 'Table'[Consumption (kW)] - related('Table 2'[Generation (kW)]) < 0, 'Table'[Consumption (kW)], related('Table 2'[Generation (kW)]))

The question is how to apply multiplier in this case? (Please note that there might be 1000 multipliers in reality so having 1000 columns is not right approach.