Forum Discussion

HowAreYou's avatar
HowAreYou
New Member
1 year ago

Iterative row calculation

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 consum...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.

  • HowAreYou's avatar
    HowAreYou
    New Member
    1 year ago

    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.