% Calculation over a period of time for a single input

[pawandilse]

Hello Community,

I have a data and looking for results as mentioned below:

Note: % is a calculated field not available in the data set.

This is done in excel, I wanted to show the trend chart in PowerBI Report,  when i tried the formula Sum(Clicks)/Sum(Sent), updated the date in x-axis and measure in y-axis, i am getting weired results in the chart.  seems like i a missing something, any help on this is much appreciated.

Regards,

Pavan

[pawandilse]

Let me give some context here, the sent's are not continuous, this happens as per the business rules, antoher sent may happen after a week or so.  I wanted to see the click % trend for the sents to understand the click behaviour.  Hope this is clear in terms of my requirement.

Please let me know in case if more details are required.

I had thought about fill down the values of the column in order to calculate the %, reason being we can freeze the cell value in excel formula while calculating the %, so not sure if that is possbile in PowerBI.

Regards,

Pavan

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

Is your source dataset the table that you have pasted?  If yes, then why are there no entries in the Sent column?  How does one have to interpret those blanks?

[lbendlin]

Since your "Sent" column has holes* you need to remove the date filter.

% = DIVIDE(sum('Table'[Clicks]),CALCULATE(sum('Table'[Sent]),REMOVEFILTERS('Table'[Date])),0)

*) You may want to rethink your data source structure.

See attached.