cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nwitstine
Frequent Visitor

Slicing accounting values based on account name

I have a simple data set with columns of data, account code, account name, date (month/year), and account activity. I'm trying to slice the data to get just activity values for accounts of a certain name, but because there are multiple results (one result for each month), I'm having trouble setting up a custom measure or column to pull this information. 

1 ACCEPTED SOLUTION
nwitstine
Frequent Visitor

Here is what I ended up doing (my table name is 'NOI 2016'):

 

  1. I created a calculated column: Month = 'NOI 2016'[Date].[Month]
  2. And a measure that counts the number of months: Months = countx('NOI 2016','NOI 2016'[Month])
  3. A measure that calculates the YTD activity: YTD = totalytd(sum('NOI 2016'[Activity]),'NOI 2016'[Date].[Date])
  4. A measure that averages the YTD activity of the applicable number of months: YTD Avg = DIVIDE('NOI 2016'[YTD],'NOI 2016'[Months])
  5. A measure that annualizes the average for 12 months (I could only figure out how to work this using the divide function, so instead of multiplying my avg by 12, I divide by 1/12 to get the same result): YTD Annualized = DIVIDE('NOI 2016'[YTD Avg],1/12)

The final measure gives me my projected annual amount based on YTD figures. In this case, I end up filtering by account name to isolate just NOI.

View solution in original post

7 REPLIES 7
nwitstine
Frequent Visitor

Here is what I ended up doing (my table name is 'NOI 2016'):

 

  1. I created a calculated column: Month = 'NOI 2016'[Date].[Month]
  2. And a measure that counts the number of months: Months = countx('NOI 2016','NOI 2016'[Month])
  3. A measure that calculates the YTD activity: YTD = totalytd(sum('NOI 2016'[Activity]),'NOI 2016'[Date].[Date])
  4. A measure that averages the YTD activity of the applicable number of months: YTD Avg = DIVIDE('NOI 2016'[YTD],'NOI 2016'[Months])
  5. A measure that annualizes the average for 12 months (I could only figure out how to work this using the divide function, so instead of multiplying my avg by 12, I divide by 1/12 to get the same result): YTD Annualized = DIVIDE('NOI 2016'[YTD Avg],1/12)

The final measure gives me my projected annual amount based on YTD figures. In this case, I end up filtering by account name to isolate just NOI.

View solution in original post

TomMartens
Super User II
Super User II

Hey,

 

I'm wondering what your expected result is ...

 

Does this simple approach not work for you:

Create a slicer from the column "account name" and a table visual that contains all the columns except "month/year".

 

The columns will  automatically be grouped and by default numeric columns will be "summed".

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

The simple approach wouldn't work because I would like to retain the month/year column.

 

Here is some sample data

Sheet 1: this how my data exports

Sheet 2: this is how I modify the data (unpivot to get month/year into a column)

 

I can easily use this data to produce NOI figures over time, however, I am trying to now get an annualized NOI figure that calculates based on the number of months. For this data set, it's easy enough to add up the 12 months of figures, however, I'd like to create a measure that "knows" how to annualize based on the number of months of data it receives. 

 

For example, if I have data through May 2018, the measure should add up 5 months, get an average and multiply by 12 for an annualized figure. 

Hi @nwitstine,

Based on my test, you can refer to below steps:
1.Create a new measure.
Measure = CALCULATE(SUM(Sheet2[Activity]),FILTER(ALL(Sheet2),'Sheet2'[Date]<=MAX('Sheet2'[Date])&& YEAR('Sheet2'[Date])=YEAR(MAX(Sheet2[Date]))))/MONTH(MAX('Sheet2'[Date]))

1.PNG

2. Create a Slicer visual and add the [Date] field. Create a card visual and add the measure. Now you can see the result controlled by the data slicer.

2.PNG

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/0la4wsxe6sydaad/Slicing%20accounting%20values%20based%20on%20account%20nam...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Daniel,

 

I'm really new to Power BI so I'm not exactly sure what your measure is doing, but when I select all months, I would expect my net operating income to equal the total from sheet 1 (17.51M), however, I am getting 4.35M. 

 

2 things that I would guess are missing:

1. I don't see anything that filters by account name so that the activity included is only for rows where the account name is "Net Operating Income." - I did try adding in an account name slicer for this, but it didn't solve anything.

2. I don't see anything that annualizes the figure (multiplies by 12). It appears to only be taking a monthly average.

Hi @nwitstine,

From your description, you said “For example, if I have data through May 2018, the measure should add up 5 months, get an average and multiply by 12 for an annualized figure.” I guess you want to calculate an average value based on the month, right? If you want to slice the values based on the account name, you just need to modify the formula:

Measure = CALCULATE(SUM(Sheet2[Activity]),FILTER(Sheet2,

    'Sheet2'[Date]<=MAX('Sheet2'[Date])&&YEAR(Sheet2[Date])=YEAR(MAX(Sheet2[Date]))&&Sheet2[Account Name]=MAX(Sheet2[Account Name])))

/MONTH(MAX('Sheet2'[Date]))

If you want to see the total value by month, you just need to create a new measure: Total value = SUM(Sheet2[Activity])

1.PNG

Now you can see when you select the all months and the “Net Operating Income”, you could get the total value “17.51M” and the measure shows the average value of the “Net Operating Income”.

Due to I am not sure what value you want to calculate, so I didn’t multiply any values, If I misunderstand you, please just let me know.

 

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/mzbe7lkmivbx9rp/Slicing%20accounting%20values%20based%20on%20account%20nam...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rajendran
Resident Rockstar
Resident Rockstar

Can you post some sample data and your expected outcome ?  If you need fix to your currnet measure, post the formula you used as well.

 

Thanks

Raj

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors