cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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

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:

 

BI Help 1.jpg

 

The Table looks like this:

 

BI Help.jpg

 

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:

 

BI Help 2.jpg

 

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

 

BI Help 3.jpg

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Super User III
Super User III

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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:

 

BI Help 4.jpg

 

Here is the sample file link:

 

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

 

Thanks

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? 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Thank you very much. This has taken me days.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors