Dividing a column by a specific row to find the percentage.

[nmyre]

Hello.

I have a table of historical data for the past couple of fiscal years. I am trying to divide one column [Obs], by the value on the last month of the year in a different column [Ann].

I tried the following:

The Table looks like this:

I am trying to DIVIDE the green [Obs] by the green [Ann] for each month. And Divide the red [Obs] by the red [Ann] for each month. Here, in yellow, is what I am looking for:

Here it is again in order by date so you can see the percantage increase each month:

Thanks.

[Ashish_Mathur]

Hi,

Do you want a calculated column or a measure solution?  Share your MS Excel file with your formula so that i can translate that into the DAX language.

[nmyre]

@Ashish_Mathur 

A measure would likely be better. It is to put into a line graph showing the % by the month. The below is not correct, the % Diff should be 0-100% and the months on the X axis will show by month, not number:

Here is the sample file link:

https://drive.google.com/file/d/1p_Z6UziWgvyYF_iAaQziI_8Ymv2Rbo2Z/view?usp=sharing

Thanks

[Ashish_Mathur]

Hi,

Please clarify the following:

1. The Fincnial Year runs from October to September; and
2. Cell W2 in the Excel file is actually October 2019 and W13 is September 2020

Is my understanding correct? 

[nmyre]

@Ashish_Mathur 

Correct. The Fiscal Year Starts in October and Ends in September. So Fiscal Year 2020 Started on October 1, 2019 and ended Sepember 30, 2020.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[nmyre]

Thank you very much. This has taken me days.

[Ashish_Mathur]

You are welcome.